Introducing the filter context and CALCULATE

Introducing CALCULATE

As you may have noticed, we have already introduced CALCULATE in most examples so far. CALCULATE is a function that changes the filter context. It adds or removes filters depending on the developer’s needs. In all previous examples, we used CALCULATE in the figures to show the different filter contexts active in different cells. CALCULATE is a DAX function that you can use to manipulate the filter context in any formula.

CALCULATE is a complex function. Its behavior is hard to learn and memorize, as are the side effects of changing the filter context. However, in this section, we want to start using CALCULATE in an easy way so that we can get acquainted with its behavior.

Let’s start with a few simple examples. If we want to compute the sales of red items, we can use CALCULATE to modify the filter context inside a measure:

Measure in the Sales table

Red Sales =
CALCULATE (
    [Sales Amount],
    'Product'[Color] = "Red"
)

This time, we do not use CALCULATE to describe the filter context of a cell in DAX. We are using CALCULATE in a measure to change the cell filter context by adding a filter for the color. When used in a matrix, the Red Sales measure shows a smaller number than the original Sales Amount, as shown in Figure 3-14.

FIGURE 3.14 Because the measure uses CALCULATE, we have two nested CALCULATE functions.

In the figure, CALCULATE shows the cell filter context applied to the evaluation of Red Sales. However, Red Sales contains another CALCULATE. Therefore, the code being executed contains two nested CALCULATE functions. Here is the code with Red Sales expanded:

CALCULATE (
    CALCULATE (
        [Sales Amount],
        Product[Color] = "Red"
    ),
    Product[Brand] = "Contoso",
    Product[Category] = "Audio"
)

The outer CALCULATE defines the filter context of the cell; the expanded code of Red Sales introduces the inner CALCULATE. In this scenario, the net effect of the two CALCULATE functions is that all the filters are active simultaneously when Sales Amount is being executed. Therefore, the cell shows the sales of (Contoso, Audio, Red). In Figure 3-15, you can see the effect of combining the two filters.

FIGURE 3.15 The filters created by the two CALCULATE functions are combined.

In this example, the two filters are intersected because they operate on different columns. Later in this chapter, we analyze what happens when two CALCULATE functions filter the same column.

When dealing with CALCULATE, we often refer to two filter contexts: the outer filter context and the inner filter context, as shown in Figure 3-16.

FIGURE 3.16 The inner filter context is generated by CALCULATE.

The outer filter context is the filter context that is active when CALCULATE is invoked. The inner filter context is the new filter context created by CALCULATE and active only during the execution of its first argument (Sales Amount in the example).

In general, the concept of inner and outer filter context is broader. When a DAX formula is executed, multiple levels of filter contexts are combined. Figure 3-17 depicts the various filter contexts that—once merged—create the active filter context of any piece of DAX code.

FIGURE 3.17 Various levels of filter contexts are merged to create the filter context of a DAX formula.

As you see, the scenario is more complex once you start diving into the details. Multiple filter contexts are merged to create the filter under which your DAX code is being executed, and this is just the beginning! The more details we are going to learn, the more complex it will be. However, step by step, we are about to uncover all the secrets of DAX evaluations.

Before we move further with CALCULATE, note how you can express filters in CALCULATE. A filter in CALCULATE can be one of three types:

• A condition, like Product[Color] = "Red". This is the most common type of condition, where you specify one or more values for a set of columns.

• A modifier, such as REMOVEFILTERS or USERELATIONSHIP. (We will introduce these in the “Introducing REMOVEFILTERS” section later in this chapter and in the “Understanding USERELATIONSHIP” section in Chapter 8, respectively.) In this case, the modifier instructs CALCULATE to perform an action: REMOVEFILTERS removes filters from the filter context, while USERELATIONSHIP activates an inactive relationship. We will cover other modifiers later on. For now, it is enough to know that they are not filters; they are instructions to CALCULATE to behave in a particular way.

• A table. If you use a table as a filter, the filter is applied to the columns present in the table with an IN condition.

Because we are at the beginning of our DAX journey, we consider a table to be a special type of condition. Hence, we avoid using tables and use an IN syntax to clarify that the filter is indeed a condition. Therefore, we will use formulas like the following:

CALCULATE (
    [Sales Amount],
    Product[Color] IN VALUES ( Product[Color] )
)

In later chapters, when we learn more about CALCULATE, we will see that tables are not a special type of condition but rather the opposite: conditions are just a shortcut for tables. In CALCULATE, the conditions are tables. However, for a few chapters, we will keep a simplified version of CALCULATE with conditions only. From time to time, when needed, we will use tables to help you start getting used to them.