# Understanding Evaluation Contexts in DAX

• 11/3/2015

## 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.

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.

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.

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