Microsoft Office Tutorials and References
In Depth Information
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
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.