Microsoft Office Tutorials and References

In Depth Information

Chapter 6

Evaluation Context and CALCULATE

Chapter 6 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 169

Evaluation Context and CALCULATE ...................... 169

To get the best from DAX, you need to understand the evaluation context. We introduced

this terminology when we talked about calculated columns and measures in Chapter 3,

“Introduction to DAX,” mentioning that calculated columns and measures differ mainly in

their evaluation context. Now let us look at how the evaluation context is defined, and most

important, how it works. In this chapter, we also introduce one of the most important DAX

functions: CALCULATE, which allows you to make complex calculations by manipulating the

evaluation context.

Note
Understanding the content of this chapter is important if you want to use DAX in Microsoft

SQL Server PowerPivot for Excel. Nevertheless, the topics described here are demanding, so do

not be afraid if some concepts seem obscure during your first read. We suggest that you read this

chapter again when you start creating your own DAX expressions; you are likely to discover that

many concepts are clearer as soon as you implement your own DAX expressions and feel the need

to better understand evaluation contexts.

understanding Evaluation Context

There are two kinds of evaluation context:

■
Filter Context
The set of active rows in a calculation.

■
Row Context
The current row in a table iteration.

We explain these in detail in the next topics.

Filter Context in a Single Table

Let us start with the filter context. When a DAX expression is evaluated, you can imagine that

for each table in the PowerPivot workbook there is a set of
active rows
, which are the only ones

that will be used for the calculation. We call this set of active rows for all the tables in the data

model a
filter context
. The filter context is a subset of all the rows, including the special cases

of the whole set of all the rows and the empty set.