Written By Rick de Groot

Rick is the founder of BI Gorilla. He believes learning is one of life's greatest pleasures and shares his knowledge to help you improve your skills.

Understanding-Evaluation-context

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 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 the evaluation context.

Table of contents

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 expression evaluates into a different result.

To understand the results, it is therefore crucial to learn about the 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

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 the picture below. The elements affecting the filter context are:

  1. Rows – filter the colours of the products
  2. Columns – filter the channel through which a product is sold
  3. Slicers – filter the Product Category
  4. 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).

Filter

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 the above picture, you can see that value A is filtered by elements 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 colour. 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 colour using a measure like:

Unique Customers = 
     DISTINCTCOUNT( FactOnlineSales[CustomerKey] )

The below picture, first of all, shows a blue shape indicating the unique customers buying ‘Economy productsin different colours. 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 make purchases in multiple categories. Summing up the unique customers within different colour categories, therefore, can result in double counting. That is because the filter context on the total level is different from the filter context on the colour level.

Filter context individual calculations

At this point, we have covered the implicit filters. These may come very naturally to you as they 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.

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, restrain 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 colour.

This means that in each of these rows, any colour filter is replaced by the colour Red in the DAX expression, even at the total level. Through this process, even colours without any customers in the ‘Product Class’ show up.

Filter Context - Filter in a DAX expression

Important to remember is that together, the implicit and explicit filters make up the filter context. The implicit filters make up the initial context, and 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 the 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 the below example, the formula to do this is stored in the calculated column called Total Sales.

Row Context in a Calculated Column

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 the row context defines this.

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 an 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 calculations 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, 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 the previous example.

Conclusion

To conclude, 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.

Share on:

Latest from my blog

Leave a comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.