The most important concept to understand DAX is evaluation context. This context is used when evaluating calculations to get the result. Some of your calculations use a visual its context as is. However, the DAX language has some built in functions that can change the evaluation context to create powerful and flexible calculations. Power BI developers will often purposely change the evaluation context using functions like CALCULATE and CALCULATETABLE. And to get comfortable doing that, you need to understand evaluation context.
What is evaluation context?
Let’s get an idea of what evaluation context is. DAX expressions are evaluated in a context. Depending on the context, an expressions evaluates into a different result. To understand the results, it is therefore crucial to learn about evaluation context in DAX. So what is it?
The evaluation context consists of two parts.
- Filter Context
- Row Context
These two contexts together make up the context in which a measure is evaluated. Yet even though they are both part of the evaluation context, I think of these two concepts as separate. They work together, but are fundamentally different from each other. So how do they work?
Understanding filter context
Let’s focus on the filter context first. The filter context is the combination of filters that is applied before the evaluation of a DAX expression. Its purpose is to filter tables. In SQL this would refer to the WHERE clause. Because of the existence of a context, the result of the same formula can be different in different cells. There are two different ways in which filters are applied.
Implicit filters you find in the user interface. When you put a measure in a visualization, the result is different in each cell because it is calculated in a different context. This context is affected by the elements in red in below picture. The elements affecting the filter context are:
- Rows – filter the colors of the products
- Columns – filter the channel through which a product is sold
- Slicers – filter the Product Category
- Filters – filter the Date on which a sale was
* note that the columns and rows from visualizations can also cross-filter others. The cross-filtering would then be applied using the rows (step 1) or columns (step 2).
Each of these elements contributes to the filter context. The DAX engine prepares this context before evaluating a DAX expression. Within the same visual, a measure can use different contexts for a calculation, depending on the location in the visual/table. How is this possible? Well, cell values in a table are calculated independently, cell by cell.
In above picture, you can see that value A is filtered by element 1, 2, 3 and 4. Whereas in that same visual, the total at B is only filtered by 2, 3 and 4. The same expression is evaluated, but without the filter on color. It would be a mistake to think that the total is always a SUM of all the other values in the visualization.
For example, imagine calculating the number of unique customers by product class and color using a measure like:
Unique Customers = DISTINCTCOUNT( FactOnlineSales[CustomerKey] )
Below picture first of all shows a blue shape indicating the unique customers buying ‘Economy products‘ in different colors. Interestingly, notice how the red shape at the grand total level shows a number that is much lower than the sum of the above. This shows that the total level is not the sum of its children. This makes sense because customers can do purchases in multiple categories. Summing up the unique customers within different color categories therefore can result in double counting. That is because the filter context on the total level is different than the filter context on the color level.
At this point we have covered the implicit filters. These may come very natural to you as it may work in the way you expect. Nevertheless, keep in mind that for more complex calculations it is of paramount importance to know what the engine does. Especially since this is only part of the filter context. The real DAX experts define adjusted filter contexts themselves, using more explicit filters.
Next to the implicit filters, one can also apply more explicit filters. Where do you find those? In your DAX expressions. The filter arguments in a DAX expression, restraint or include values in a column or table. In other words, these filter arguments take the implicit filter context and then either add or remove a filter. Functions like ALL, ALLSELECTED, REMOVEFILTERS, FILTER, KEEPFILTERS are often used to do this.
For example, let’s imagine you change the Unique Customers expression to:
//Short, syntax: Unique Customers - Red = CALCULATE( DISTINCTCOUNT( FactOnlineSales[CustomerKey] ), DimProduct[ColorName] = "Red" ) // Which is syntax sugar for: Unique Customers - Red = CALCULATE( DISTINCTCOUNT( FactOnlineSales[CustomerKey] ), FILTER( ALL( DimProduct[ColorName] ), DimProduct[ColorName] = "Red" ) )
What happens is that (1) in each row the implicit filter context is taken. This would result in the picture we saw earlier. Then before the DAX expression is evaluated, (2) the CALCULATE statement overrides the filter on a color. Meaning that in each of these rows, any color filter is replaced by the color Red in the DAX expression, even at the total level. Through this process, even colors without any customers in the ‘Product Class’ show up.
Important to remember is that together the implicit and explicit filters make up the filter context. The implicit filters make up the initial context, the explicit filters then alter this context. And lastly the DAX expression is evaluated. However, a concept that’s still missing is Row Context.
Understanding Row Context
The previous section delved into the filter context. You learned that its goal is to filter. The row context is the other context that contributes to the evaluation context. In this section you will learn how to think of row context.
Row context is used to calculate an expression row-by-row. You may have heard that the row context iterates through a table. This means that it performs a calculation row by row within a table. A simple example of a row context is the following.
Below is a sample of sales table. You can find a column for the UnitPrice and UnitsSold. To retrieve the total sales you multiply the UnitPrice by the UnitsSold. In below example the formula to do this is stored in the calculated column called Total Sales.
Notice how within a calculated column, you can make a column reference for your calculations. In the calculated column, the multiplication of the UnitPrice by the UnitsSold is done row by row. Now this may seem obvious, but we have not instructed DAX to do a row-by-row calculation. After all, it only indicates multiplying the columns. So how does it know from which row it should multiply the numbers? The answer is that this is defined by the row context.
When you create a calculated column, the engine automatically creates a row context containing only the row that the calculation is performed on.
Earlier the filter context explained the difference in numbers. In this example the difference in numbers in the calculated column is explained by the existence of the row context. The existence of the row context, makes it so there is no need to wrap a aggregation function, like SUM, around the columns.
Row context is involved both when creating calculated columns and when you’re using certain measures. The typical functions that iterate are ‘X’ functions like SUMX, AVERAGEX, MAXX, but also functions like FILTER and some others.
You can also perform previous calculation without a calculated column. In this case you would use a measure, like the one below:
Total Sales = SUMX ( Sales, Sales[UnitPrice] * Sales[UnitsSold] ) )
Measures don’t have a row context by default. However, the measures that are considered iterators automatically create a row context. SUMX being an iterator, it requires as first argument a table to iterate. In this case we provide the Sales table. It then creates a row context within the sales table, and performs the calculation in the second argument row-by-row. The existence of the row context then allows you to reference column names as in previous example.
In summary, the row context does not filter tables in any way. The filtering is done by the filter context (implicit and explicit filters). However, to perform a calculation row-by-row the row context is used.