Introducing the filter context and CALCULATE

In the previous chapter, we introduced the basic concepts of DAX and showcased some of the most used functions. However, the real power of DAX is not in knowing each function; the real power of DAX is in manipulating the filter context, along with a good understanding of the row context. In this chapter, we introduce the filter context and the CALCULATE function in their simplest form. We will not explore the most obscure intricacies of the evaluation context and CALCULATE. However, trust us when we say there are many of those complexities. As with any other journey, the trip to learning the evaluation context starts with the first step.

We want to give a few words of warning to our readers. The concept of evaluation context is simple, and you will learn and understand it soon. Nevertheless, you need to thoroughly understand several subtle considerations and details before you really master DAX. Otherwise, you will feel lost at a certain point on your DAX learning path. We have been teaching DAX to thousands of students in public and private classes, so we know this is normal.

The content of this chapter is not enough to explain the intricacies of the DAX evaluation context. It is a good starting point, but there will be many more details to learn. Hence, if some code is unclear, do not worry too much. Things will become clearer and clearer in the process of reading the first chapters of this book. Then, after the theoretical background is established, you can look at the code with a deep understanding of how it works.

Introducing axis and coordinates

Before introducing the filter context, let’s introduce the concept of the axis of a visual and the coordinates of a point in the visual. A Power BI visual may have zero, one, or two axes. A card visual has no axis because the content of the visual is just a number. A table visual has one axis: the rows. A matrix has two axes: rows and columns. In Figure 3-1, you can see these three visuals with their axes.

FIGURE 3.1 Power BI visuals can have zero, one, or two axes.

Based on the axes available for a visual, we can define a coordinate system to describe one individual cell of the visual. For the card visual, there are no coordinates. Therefore, there is no need to define a coordinate system: We can reference the one and only cell of the visual just by the cell or the value.

When it comes to the table visual, there is only one dimension: the rows axis. In our example, on the rows axis, there are two model columns from the Customer table: Customer[Continent] and Customer[Gender]. We provide the values for the two columns to define a row. For example, the pair (Australia, Female) defines the first row of the table visual. It is useful to note that (Australia, Female) defines a row of the visual, not just a single cell. To point out a single cell, we must also provide a measure, like Sales Amount. However, the measure does not belong to the coordinate system. The measure defines the calculation happening in that cell. The coordinates of a cell are made up of the model table’s column names. In other words, both Sales Amount and Margin share the same coordinates (Australia, Female).

In a matrix visual, the coordinates are even clearer, mainly because we used only one measure. Both the rows and the columns of the matrix contribute to the coordinates of a cell. In the matrix visual shown, there is only one measure (Sales Amount), and the coordinate system is identical to the one used in the table visual: the coordinates of a cell are made up of the model table’s column names. The matrix visual would be harder to read if we used two measures. However, as was the case with the table visual, the measure does not contribute to the coordinates; it only defines the algorithm executed in that cell.

It is worth noting that—given the same set of coordinates—a measure computes the same value in any visual. The (Australia, Female) combination computes the value of Sales Amount identically in the table and matrix visuals.

The coordinates of a cell contribute to the definition of the filter under which a formula is computed. In a more complex scenario, where the coordinate system includes more columns, the filter becomes more complex, as shown in Figure 3-2.

FIGURE 3.2 All the coordinates of a cell contribute to creating the filter context.

The filter operating on a cell is the filter context. Be mindful that the coordinates of a cell are just one part of the filter context. The filter context is a bit more complex than just the set of coordinates, as we will find out in the next section. For now, consider the coordinates and the filter context to be the same thing.

A concept that may not be obvious at first sight is the coordinates of subtotals. The coordinates of subtotals do not include some of the axes’ elements. For example, in Figure 3-3, you can see the coordinates of a subtotal for both the rows and columns axes: It does not include either the gender or the category.

FIGURE 3.3 Coordinates of subtotal cells do not filter all the columns of the axes.

A Power BI visual can be more complex than that. For example, using small multiples, one could create a visual that—by definition—contains multiple visuals. Or it could be that the axes are not immediately recognizable. For example, the visual in Figure 3-4 shows multiple charts by category, each with the year in the x-axis and the customer continent as the legend.

FIGURE 3.4 In a complex visual, each cell has a defined set of filters, even though the rows and columns axes are less recognizable.

Whether the legend should be the rows, the columns, or the filter of the visual context is debatable. The same is true for the column used for small multiples. Despite the concept being somewhat ambiguous, we just rely on an intuitive approach. From a DAX standpoint, it does not matter whether a filter is on the rows, columns, small multiples, legend, or filter. In the end, regardless of where it comes from, it is a filter that contributes to the filter context under which a formula is being evaluated. Indeed, coordinates are mainly useful in defining the filter context, as we will find out in the next section.