Understanding Evaluation Contexts in DAX

At this point in the book, you have learned the basics of the DAX language. You know how to create calculated columns and measures, and you have a good understanding of common functions used in DAX. This is the chapter where you move to the next level in this language: After learning a solid theoretical background of the DAX language, you will be able to become a real DAX champion.

With the knowledge you have gained so far, you can already create many interesting reports, but you will need to learn evaluation contexts in order to create reports that are more complex. Evaluation contexts are the basis of all of the advanced features of DAX.

We want to give a few words of warning to our readers: The concept of evaluation context is an easy one, and you will learn and understand it soon. Nevertheless, you need to thoroughly understand several subtle considerations and details. Otherwise, you will feel lost at a certain point during your DAX learning path. We teach DAX to many users in public and private classes. We know that this is absolutely normal. At a certain point—you have the feeling that formulas work like magic, because they work, but you do not understand why. Do not worry: you will be in good company. Most DAX students reach that point and many others will reach it in the future. It simply means that evaluation contexts are not clear enough to them. The solution, at that point, is an easy one: Come back to this chapter, read it again, and you will probably find something new, which you missed during your first read.

Moreover, evaluation contexts play an important role with the usage of the function CALCULATE, which is probably the most powerful and hard-to-learn function in DAX. We will introduce CALCULATE in Chapter 5, “Understanding CALCULATE and CALCULATETABLE,” and then use it through the rest of the book. Understanding CALCULATE without having a solid background on evaluation context is problematic. On the other hand, understanding the importance of evaluation contexts without having ever tried to use CALCULATE is nearly impossible. Thus, this chapter and the subsequent one are the two that, in our experience with the previous books we have written, are always marked up and have the corners of pages folded over.

Introduction to evaluation contexts

Let’s begin by understanding what an evaluation context is. Any DAX expression is evaluated inside a context. The context is the “environment” under which the formula is evaluated. For example, consider a very simple formula for a measure such as:

[Sales Amount] := SUMX ( Sales, Sales[Quantity] * Sales[UnitPrice] )

You already know what this formula computes: the sum of all the values of quantity multiplied by price in the Sales table. You can put this measure in a pivot table and look at the results, as you can see in Figure 4-1.

FIGURE 4-1 The measure Sales Amount, without a context, shows the grand total of sales.

Well, this number alone does not look interesting at all, does it? But, if you think carefully, the formula computes exactly what it is supposed to compute: the sum of all sales amount, which is a big number with no interesting meaning. This pivot table becomes more interesting as soon as we use some columns to slice the grand total and start investigating it. For example, you can take the product color, put it on the rows, and the pivot table suddenly reveals some interesting business insights, as you can see in Figure 4-2.

FIGURE 4-2 Sum of Sales Amount, sliced by color, looks much more interesting.

The grand total is still there, but now it is the sum of smaller values and each value, together with all the others, has a meaning. However, if you think carefully again, you should note that something weird is happening here: the formula is not computing what we asked.

We supposed that the formula meant “the sum of all sales amount.” but inside each cell of the pivot table, the formula is not computing the sum of all sales, it is only computing the sum of sales of products with a specific color. Yet, we never specified that the computation had to work on a subset of the data model. In other words, the formula does not specify that it can work on subsets of data.

Why is the formula computing different values in different cells? The answer is very easy, indeed: because of the evaluation context under which DAX computes the formula. You can think of the evaluation context of a formula as the surrounding area of the cell where DAX evaluates the formula.

Because the product color is on the rows, each row in the pivot table can see, out of the whole database, only the subset of products of that specific color. This is the surrounding area of the formula, that is, a set of filters applied to the database prior to the formula evaluation. When the formula computes the sum of all sales amount, it does not compute it over the entire database, because it does not have the option to look at all the rows. When DAX computes the formula in the row with the value White, only white products are visible and, because of that, it only considers sales pertinent to white products. So the sum of all sales amount, when computed for a row in the pivot table that shows only white products, becomes the sum of all sales amount of white products.

Any DAX formula specifies a calculation, but DAX evaluates this calculation in a context, which defines the final value computed. The formula is always the same, but the value is different because DAX evaluates it against different subsets of data.

The only case where the formula behaves in the way it has been defined is on the grand total. At that level, because no filtering happens, the entire database is visible.

NOTE

In these examples, we are using a pivot table for the sake of simplicity. Clearly, you can define an evaluation context with queries too, and you will learn more about it in future chapters. For now, it is better to keep it simple and think of pivot tables only, so as to have a simplified and visual understanding of the concepts.

Now let’s put the year on the columns, to make the pivot table even more interesting. The report is now the one shown in Figure 4-3.

FIGURE 4-3 Sum of SalesAmount is now sliced by color and year.

The rules of the game should be clear at this point: Each cell now has a different value even if the formula is always the same, because both row and column selections of the pivot table define the context. In fact, sales for white products in year 2008 are different from sales for white products in 2007. Moreover, because you can put more than one field in both rows and columns, it is better to say that the set of fields on the rows and the set of fields on the columns define the context. Figure 4-4 makes this more evident.

FIGURE 4-4 The context is defined by the set of fields on rows and on columns.

Each cell has a different value because there are two fields on the rows, color and brand name. The complete set of fields on rows and columns defines the context. For example, the context of the cell highlighted in Figure 4-4 corresponds to color Black, brand Contoso, and Calendar Year 2007.

NOTE

It is not important whether a field is on the rows or on the columns (or on the slicer and/or page filter, or in any other kind of filter you can create with a query). All of these filters contribute to define a single context, which DAX uses to evaluate the formula. Putting a field on rows or columns has just some aesthetic consequences, but nothing changes in the way DAX computes values.

Let’s see the full picture now. In Figure 4-5, we added the product category on a slicer, and the month name on a filter, where we selected December.

FIGURE 4-5 In a typical report, the context is defined in many ways, including slicers and filters.

It is clear at this point that the values computed in each cell have a context defined by rows, columns, slicers, and filters. All these filters contribute in the definition of a context that DAX applies to the data model prior to the formula evaluation. Moreover, it is important to learn that not all the cells have the same set of filters, not only in terms of values, but also in terms of fields. For example, the grand total on the columns contains only the filter for category, month, and year, but it does not contain the filter for color and brand. The fields for color and brand are on the rows and they do not filter the grand total. The same applies to the subtotal by color within the pivot table: for those cells there is no filter on the manufacturer, the only valid filter coming from the rows is the color.

We call this context the Filter Context and, as its name suggests, it is a context that filters tables. Any formula you ever author will have a different value depending on the filter context that DAX uses to perform its evaluation. This behavior, although very intuitive, needs to be well understood.

Now that you have learned what a filter context is, you know that the following DAX expression should be read as “the sum of all sales amount visible in the current filter context”:

[Sales Amount] := SUMX ( Sales, Sales[Quantity] * Sales[UnitPrice] )

You will learn later how to read, modify, and clear the filter context. As of now, it is enough having a solid understanding of the fact that the filter context is always present for any cell of the pivot table or any value in your report/query. You always need to take into account the filter context in order to understand how DAX evaluates a formula.

Understanding the row context

The filter context is one of the two contexts that exist in DAX. Its companion is the row context and, in this section, you will learn what it is and how it works.

This time, we use a different formula for our considerations:

Sales[GrossMargin] = Sales[SalesAmount] - Sales[TotalCost]

You are likely to write such an expression in a calculated column, in order to compute the gross margin. As soon as you define this formula in a calculated column, you will get the resulting table, as shown in Figure 4-6.

FIGURE 4-6 The GrossMargin is computed for all the rows of the table.

DAX computed the formula for all the rows of the table and, for each row, it computed a different value, as expected. In order to understand the row context, we need to be somewhat pedantic in our reading of the formula: we asked to subtract two columns, but where did we tell DAX from which row of the table to get the values of the columns? You might say that the row to use is implicit. Because it is a calculated column, DAX computes it row by row and, for each row, it evaluates a different result. This is correct, but, from the point of view of the DAX expression, the information about which row to use is still missing.

In fact, the row used to perform a calculation is not stored inside the formula. It is defined by another kind of context: the row context. When you defined the calculated column, DAX started an iteration from the first row of the table; it created a row context containing that row and evaluated the expression. Then it moved on to the second row and evaluated the expression again. This happens for all the rows in the table and, if you have one million rows, you can think that DAX created one million row contexts to evaluate the formula one million times. Clearly, in order to optimize calculations, this is not exactly what happens; otherwise, DAX would be a very slow language. Anyway, from the logical point of view, this is exactly how it works.

Let us try to be more precise. A row context is a context that always contains a single row and DAX automatically defines it during the creation of calculated columns. You can create a row context using other techniques, which are discussed later in this chapter, but the easiest way to explain row context is to look at calculated columns, where the engine always creates it automatically.