Introducing the filter context and CALCULATE

VALUES as an alternative to KEEPFILTERS

We introduced VALUES in the previous chapter. However, let’s recap briefly what we know about the function before discussing how VALUES can be used as an alternative to KEEPFILTERS. The VALUES table function returns the values of a column, as currently visible in the filter context. For example, if you need to compute the number of colors sold by each brand, you can author a measure that just counts the number of rows in the result of VALUES:

Measure in the Sales table

# Colors = COUNTROWS ( VALUES ( 'Product'[Color] ) )

When used in a matrix, the measure shows the number of colors of each brand. Indeed, VALUES is evaluated in the cell filter context, which only shows one brand. Despite no explicit filter being placed on the Product[Color] column, only the colors sold by the current brand are visible because Product[Color] is being cross-filtered by the filter on Product[Brand]. In Figure 3-26, you can see that when VALUES is evaluated for Adventure Works, it returns seven values.

FIGURE 3.26 VALUES is evaluated for the Adventure Works brand, showing only 7 out of 16 colors.

VALUES is extremely useful in CALCULATE because it lets the developer access the values of a column that are visible in the outer filter context. In some scenarios, it is helpful to reproduce a filter that CALCULATE would replace. We now observe how it works by analyzing an alternative version of Red Colors that—instead of using KEEPFILTERS—leverages VALUES to re-create the outer filter context into the inner filter context.

When using KEEPFILTERS, this is the code of Red Sales we created:

Measure in the Sales table

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

KEEPFILTERS aims to ensure that the outer filter over Product[Color] is maintained in the inner filter context. The same goal, which is to re-create the outer filter context in the inner filter context, can be reached by using VALUES with this alternative formulation, where IN checks that the Product[Color] values are within the list of colors visible in the current filter context:

Measure in the Sales table

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

As you can see in Figure 3-27, the result is the same as Red Sales using KEEPFILTERS: The sales amount value is shown only for the row containing Red.

FIGURE 3.27 Red Sales shows the sales amount value only when Red is already in the cell filter context.

Understanding this latter version of the code is important. CALCULATE’s task is to create the inner filter context. Once the new filter context is ready, it becomes the inner filter context, under which Sales Amount is to be computed. However, while the new filter context is being prepared, the outer filter context remains active. As part of creating the new filter context, CALCULATE evaluates its filter arguments: In this case, CALCULATE checks that the product color needs to be within the list of colors returned by VALUES ( Product[Color] ). When VALUES ( Product[Color] ) is computed, the active filter context is still the outer filter context; therefore, it contains the seven rows shown earlier.

In Figure 3-28, you can observe the formula’s behavior for the Black row in Figure 3-27.

FIGURE 3.28 VALUES is evaluated when the outer filter context only filters Black.

There is a difference between using KEEPFILTERS and using VALUES. As its name implies, KEEPFILTERS keeps existing filters. VALUES creates a filter despite the filter not being there in the first place. Right now, this may seem overly complicated. When KEEPFILTERS is enough, it is also faster than VALUES. However, we will see that VALUES is helpful in more complex calculations. In the following sections, we showcase a scenario where using VALUES is important.