Understanding Evaluation Contexts in DAX

Testing your evaluation context understanding

Before we move on with more complex discussions about evaluation contexts, we would like to test your understanding of contexts with a couple of examples. Please do not look at the explanation immediately; stop after the question and try to answer it. Then read the explanation to make sense out of it.

Using SUM in a calculated column

The first test is a very simple one. What is happening if you define a calculated column, in Sales, with this code?

Sales[SumOfSalesAmount] = SUM ( Sales[SalesAmount] )

Because it is a calculated column, it will be computed row by row and, for each row, you will obtain a result. What number do you expect to see? Choose one from among these options:

• The value of SalesAmount for that row, that is, a different value for each row.
• The total of SalesAmount for all the rows, that is, the same value for all the rows.
• An error; you cannot use SUM inside a calculated column.

Stop reading, please, while we wait for your educated guess before moving on.

Now, let’s elaborate on what is happening when DAX evaluates the formula. You already learned what the formula meaning is: “the sum of all sales amount as seen in the current filter context.” As this is in a calculated column, DAX evaluates the formula row by row. Thus, it creates a row context for the first row, and then invokes the formula evaluation and proceeds iterating the entire table. The formula computes the sum of all sales amount values in the current filter context, so the real question is “What is the current filter context?” Answer: It is the full database, because DAX evaluates the formula outside of any pivot table or any other kind of filtering. In fact, DAX computes it as part of the definition of a calculated column, when no filter is active.

Even if there is a row context, SUM ignores it. Instead, it uses the filter context and the filter context right now is the full database. Thus, the second option is correct: You will get the grand total of sales amount, the same value for all the rows of Sales, as you can see in Figure 4-7.

FIGURE 4-7 SUM ( Sales[SalesAmount] ), in a calculated column, is computed against the full database.

This example shows that the two contexts exist together. They both work on the result of a formula, but in different ways. Aggregate functions like SUM, MIN, and MAX used in calculated columns use the filter context only and ignore the row context, which DAX uses only to determine column values. If you have chosen the first answer, as many students typically do, it is perfectly normal. The point is that you are not yet thinking that the two contexts are working together to change the formula result in different ways. The first answer is the most common, when using intuitive logic, but it is the wrong one, and now you know why.

Using columns in a measure

The second test we want to do with you is slightly different. Imagine you want to define the formula for gross margin in a measure instead of in a calculated column. You have a column with the sales amount, another column for the product cost, and you might write the following expression:

[GrossMargin] := Sales[SalesAmount] - Sales[ProductCost]

What result should you expect if you try to author such a measure?

1. The expression works correctly, we will need to test the result in a report.
2. An error, you cannot even author this formula.
3. You can define the formula, but it will give an error when used in a pivot table or in a query.

As before, stop reading, think about the answer, and then read the following explanation.

In the formula, we used Sales[SalesAmount], which is a column name, that is, the value of SalesAmount in the Sales table. Is this definition lacking something? You should recall, from previous arguments, that the information missing here is the row from where to get the current value of SalesAmount. When you write this code inside a calculated column, DAX knows the row to use when it computes the expression, thanks to the row context. However, what happens for a measure? There is no iteration, there is no current row, that is, there is no row context.

Thus, the second answer is correct. You cannot even write the formula; it is syntactically wrong and you will receive an error when you try to enter it.

Remember that a column does not have a value by itself. Instead, it has a different value for each row of a table. Thus, if you want a single value, you need to specify the row to use. The only way to specify the row to use is the row context. Because inside this measure there is no row context, the formula is incorrect and DAX will refuse it.

The correct way to specify this calculation in a measure is to use aggregate functions, as in:

[GrossMargin] := SUM ( Sales[SalesAmount] ) - SUM ( Sales[ProductCost] )

Using this formula, you are now asking for an aggregation through SUM. Therefore, this latter formula does not depend on a row context; it only requires a filter context and it provides the correct result.