In order to understand context transition, one must understand many of the building blocks that lead to this concept. It certainly is not a beginning concept, and one would probably benefit from writing lots of DAX before attempting to gain a fuller understanding. Not knowing this concept will stymie your best efforts to write effective DAX code.
I will assume an intermediate level of knowledge. You will know the following:
- Iterators and calculated columns have row context.
- Initial filter context includes rows, columns, filters, slicers and other visuals in the report.
- Filter propagation automatically moves in the direction of the one to the many in a table relationship. (It is possible to filter a look up table based on the results of a data table by using DAX instead of filter propagation.)
- A row context does not automatically create a filter context.
- A row context does not follow relationships. It does not create a filter context and therefore is not propagated.
- Row context refers to the ability of an iterating function or a calculated column to be “aware” of which row it is acting on at each stage of the formula evaluation.
- An explicit CALCULATE is one which is evident in the DAX formula.
- The engine creates an implicit CALCULATE in every measure.
- The concept of context transition can exist anywhere that row context exists.
- FILTER is an iterator which takes a table, and filters it, returning a virtual table.
- A virtual table retains a link to the table(s) from which it was created. This is known as lineage.
Assuming this knowledge, let us move onto CALCULATE. Everything that is discussed here also applies to CALCULATETABLE. These two functions are not only powerful, their evaluation order is different than other DAX functions. CALCULATE takes an expression, can take multiple filters, or modifiers and returns a scalar value.
If we have a simple model as above, with Customers and Sales, we can see how context transition works. Below we have two measures: [Total Sales] and [Count of Customers with Lifetime Sales over 100K]
The evaluation order is as follows. In CALCULATE, the filter is evaluated first. The implicit CALCULATE turns the row context in Customers to a filter context. Thus, for the first customer, the Customers[ID] is propagated to the Sales table as a filter, and the sum of all the Sales for that customer is compared against the filter. Is it greater than or equal to 100K? If so, it remains in the table. If not, it is filtered out. The next customer is then evaluated using the filter context.
The FILTER function returns a virtual table (with lineage to the Customers table in the data model) which is used to filter the Customers table. COUNTROWS then counts the remaining rows in the Customers table.
In the final analysis, although CALCULATE has a simple syntax, it is a very powerful tool based on all that it can be used to do. It is extremely important to understand the evaluation order of CALCULATE and that it is different than other DAX functions. CALCULATE and CALCULATETABLE are the only functions that use context transition. Once context transition is understood, we need to dig deeper into the interactions between CALCULATE and other functions in DAX.
Nathaniel Cohen 