Introducing the filter context and CALCULATE

Introducing the filter context

The evaluation context has two parts: the filter context and the row context. This chapter introduces the most important of the two: the filter context. We will describe the row context in Chapter 5, “Introducing the row context and the context transition,” after we understand the filter context well.

The filter context is the set of filters, mostly created by visuals, under which the DAX code is evaluated. For example, the report in Figure 3-5 shows the Sales Amount sliced by Product[Brand].

FIGURE 3.5 The report shows the Sales Amount measure sliced by brand.

Each row shows the Sales Amount of the given Brand. Despite the behavior being very intuitive, it is important to understand how DAX generates the different numbers. Every cell of the matrix computes the same formula: the Sales Amount measure. The Sales Amount code is a simple SUMX over the Sales table:

Measure in the Sales table

Sales Amount =
SUMX (
    Sales,
    Sales[Quantity] * Sales[Net Price]
)

The Sales Amount measure does not include any filter. The formula scans Sales and computes the multiplication of Quantity times Net Price as part of the iteration. However, every cell in the matrix shows a different result, indicating that the calculation is happening under a filter. The filter is not inside the formula but outside the DAX code. This is why we call this filter a context: It is a filter that the formula finds itself in. The context filters the model; hence the name, filter context.

Each cell of the matrix is executed in its own filter context. In this first simple example, the filter context is entirely defined by the coordinates of the cell. In each cell, the value of Sales Amount is computed by filtering the cell coordinates. When the code of the measure is being executed, the filter is active, thus reducing the number of rows in Sales that are visible to SUMX. Figure 3-6 shows how the filter originates from the row in the matrix and is transferred to the formula in the code of the measure.

FIGURE 3.6 The filter context originates from the row of the matrix, and it affects the code of the measure.

The filter context determines the calculation in the cell, as it filters which content to apply the formula to. The filter context filters columns in the data model. In our example, the filter context filters the Product[Brand] column, allowing only the row for Tailspin Toys to be visible.

Different query languages use different ways of applying filters. In DAX, we use CALCULATE to create or modify the filter context. Therefore, a more correct and DAX-like representation of the filter being applied to the measure is the one in Figure 3-7.

FIGURE 3.7 CALCULATE is the function that modifies the filter context in DAX.

Before we move forward, let’s say a few words about CALCULATE. CALCULATE accepts many arguments. The first is the expression to compute. Starting from the second argument, CALCULATE receives the filters to create or modify the filter context. The filters are applied before the first argument can be computed; therefore, they must be evaluated first. This makes CALCULATE somewhat counterintuitive. CALCULATE starts evaluating its second argument, and then its third, fourth, and so on; only at the end does CALCULATE compute its first argument after creating the new filter context. Despite this being somewhat counterintuitive, you will use CALCULATE so often that it will become natural once you are used to it.

A matrix can be more complex than the one shown in this first example. Adding more model columns to the rows axis is possible, like in Figure 3-8. However, the mechanism is the same: In the eyes of CALCULATE, each additional column just acts as an additional filter.

FIGURE 3.8 Multiple filters can be used in CALCULATE, and they add to the filter context.

CALCULATE accepts any number of arguments as filters. They are all intersected, meaning that all the filters work together at once. Indeed, if we further modify the matrix by adding the years on the columns, that operation changes the filter but not the mechanism used to create the filter itself. In Figure 3-9, we simplify the diagram by using one box for the visual representation of the filter context and another box for the equivalent CALCULATE formula without starting from the original measure definition.

FIGURE 3.9 Values on rows and columns all contribute to the filter context of a cell.

You might notice that the filters applied by CALCULATE operate on Product and Date, whereas the measure scans Sales. At this point, a natural question one might have becomes, how do the filters propagate from one table to another? The answer lies in the presence of relationships.

Placing a filter on one table automatically propagates its effect to any table with a relationship with the original table, following the cross-filter direction of the relationship. The cross-filter direction of a relationship is visible in the diagram view of Power BI as a small arrow in the middle of the relationship itself, as visible in Figure 3-10.

FIGURE 3.10 Relationships define how the filter context propagates the filters through the cross-filter direction.

As you can see, both Product and Date filter Sales. Therefore, when CALCULATE applies a filter on either Product or Date, the filter is automatically transferred to Sales. It is worth remembering that the structure of the data model is an integral part of the DAX code. Changing the model changes the way numbers are computed. Namely, changing the cross-filter direction of a relationship strongly impacts how the filters are transferred.

The filter context is generated not only by the coordinates of the current visual but also by other visuals. In a Power BI report, multiple visuals may contribute to the filter context of one cell—slicers, for instance, do this. Whenever you select one or more items in a slicer, the selection generates a filter added to the filter context, as shown in Figure 3-11.

FIGURE 3.11 Slicers and other visuals contribute to the filter context of a cell.

Whenever developers need to focus on a cell and understand its filter context, they should consider both the current visual and any other visual cross-filtering it.

A filter context is active in every cell of the matrix. The columns that contribute to its definition are the columns from the model that contribute to the coordinates of the cell. Think about the subtotals, as shown in Figure 3-12: The coordinates of the subtotals do not include Product[Category]. Hence, Product[Category] is not being filtered in the cell filter context.

FIGURE 3.12 At the subtotal level, the more detailed columns do not contribute to the cell filter context.

You have now seen several examples, so the rules of the game should be clear by now.

For the sake of clarity, as we expand on the above, we’ll refer to the visual we’re focusing on and whose filter context we are analyzing as the “current visual.” Each visual in the report can cross-filter other visuals and, therefore, add its own filter to the filter context of a cell in the current visual. Most visuals can apply filters. Slicer selection is a standard filter, but for example, the selection of elements in a bar chart can also cross-filter other visuals. A selection you may make in a visual other than the current visual will add filters on all the columns used to create the current visual. In a matrix, both rows and column axes contribute to the filter. In a bar chart, individual bars add their filter context to the filter context of the current visual.

So far, we have expanded the Sales Amount code in all the figures to make the code being executed more evident. From now on, we will use a more compact representation, using only the measure name. The compact form is shown in Figure 3-13.

FIGURE 3.13 In this figure, the code executed by CALCULATE is the measure name, no longer its expansion.

We will expand the code occasionally when we need to go into more detail. However, the compact version is shorter and easier to read.

Let’s briefly recap what we have learned so far about the filter context:

• Each visual has its filter context. We call it the visual filter context. The visual filter context is determined by all the other visuals in the report that interact with that current visual and all the report filters applied through the filters pane (visual-, page-, and report-level filters). Slicers, column charts, line charts…most of the visuals in a report contribute to the filter context of the current visual.

• Each cell has its filter context. We call it the cell filter context. The coordinates of the cell itself determine the filter context of a cell, plus the visual filter context. The value shown for a cell is determined by the measure used in the cell, which is executed in the cell filter context.

• At the subtotal level, the cell coordinates do not include all the columns. Therefore, the number of columns filtered may be different for different cells.

• At the grand-total level, the cell filter context is the same as the visual filter context because the coordinates of the grand total do not include any column of the visual axes.

Being able to detect the filter context of a cell quickly is of paramount importance when developing DAX formulas. We will analyze several scenarios where the filter context is much more complex than the simple examples provided. However, we proceed one step at a time; for now, the goal is only to get a feeling of what the filter context is.