Understanding Evaluation Contexts in DAX

Understanding FILTER, ALL, and context interactions

In the preceding example, we have used FILTER as a convenient way of filtering a table. FILTER is a very common function to use, whenever you want to apply a filter that further restricts the existing context.

Imagine that you want to create a measure that counts the number of red products. With the knowledge you gained so far, the formula is an easy one:

[NumOfRedProducts] :=
COUNTROWS (
    FILTER (
        Product,
        Product[Color] = "Red"
    )
)

This formula works fine and you can use it inside a pivot table; for example, putting the brand on the rows to produce the report shown in Figure 4-10.

FIGURE 4-10 You can easily count the number of red products using the FILTER function.

Before moving on with this example, it is useful to stop for a moment and think carefully how DAX computed these values. The brand is a column of the Product table. The engine evaluates NumOfRedProducts inside each cell, in a context defined by the brand on the rows. Thus, each cell shows the number of red products that also have the brand indicated by the corresponding row. This happens because, when you ask to iterate over the Product table, you are really asking to iterate the Product table as it is visible in the current filter context, which contains only products with that specific brand. It might seem trivial, but it is better to remember it multiple times than take a chance of forgetting it.

This is more evident if you put a slicer on the worksheet containing the color. In Figure 4-11 we have created two identical pivot tables with the slicer on color. You can see that the left one has the color Red selected, and the numbers are the same as in Figure 4-10, whereas in the right one the pivot table is empty because the slicer has the color Green selected.

FIGURE 4-11 DAX evaluates NumOfRedProducts taking into account the outer context defined by the slicer.

In the right pivot table, the Product table passed into FILTER contains only Green products and, because there are no products that can be red and green at the same time, it always evaluates to BLANK (that is, FILTER does not return any row that COUNTROWS can work on).

The important part of this example is the fact that, in the same formula, there are both a filter context coming from the outside (the pivot table cell, which is affected by the slicer selection) and a row context introduced in the formula. Both contexts work at the same time and modify the formula result. DAX uses the filter context to evaluate the Product table, and the row context to filter rows during the iteration.

At this point, you might want to define another formula that returns the number of red products regardless of the selection done on the slicer. Thus, you want to ignore the selection made on the slicer and always return the number of the red products.

You can easily do this by using the ALL function. ALL returns the content of a table ignoring the filter context, that is, it always returns all the rows of a table. You can define a new measure, named NumOfAllRedProducts, by using this expression:

[NumOfAllRedProducts] :=
COUNTROWS (
    FILTER (
        ALL ( Product ),
        Product[Color] = "Red"
    )
)

This time, instead of referring to Product only, we use ALL ( Product ), meaning that we want to ignore the existing filter context and always iterate over all products. The result is definitely not what we would expect, as you can see in Figure 4-12.

FIGURE 4-12 The NumOfAllRedProducts returns strange results.

There are a couple of interesting things to note in here:

• The result is always 99, regardless of the brand selected on the rows.
• The brands in the left pivot table are different from the ones in the right one.

Let us investigate both topics. First, 99 is the total number of red products in the database. Having used ALL, we have removed all the filters from the Product table, that is, we have removed both the filter on color and the filter on brand. This is an unwanted effect but, unfortunately, we do not have any other option with the limited knowledge we have of DAX as of now. ALL is very powerful, but it is an all-or-nothing function: if used, it removes all of the filters; it has no options to remove only part of them. To be more specific, we wanted to remove only the filter on color, leaving all other filters untouched. In the next chapter, you will learn how to solve this issue with the introduction of CALCULATE.

The second point is easier to understand: Because we have selected Green, we are seeing the manufacturers of green products, not the manufacturers of all the products. Thus, the rightmost pivot table shows green product manufacturers with the total of red products in the database. This happens because the list of manufacturers, used to populate the axis of the pivot table, is computed in the original filter context, which contains a filter on color equal to green. Once the axes have been computed, then the values are computed, always returning 99 as a result.

NOTE

This behavior of DAX is called “AutoExists logic.” It looks very natural, because it hides nonexisting values, despite some internal complexity. In Chapter 10, “Advanced evaluation context,” we will dedicate a section to describe the AutoExists behavior in full detail.

We do not want to solve this scenario right now. The solution will come later when you learn CALCULATE, which has specific features to solve scenarios like this one. As of now, we have used this example to show that you might find strange results coming from relatively simple formulas because of context interactions and the coexistence, in the same expression, of filter and row contexts.