Introducing the filter context and CALCULATE

Introducing REMOVEFILTERS

In the examples we have seen, we used CALCULATE to add or replace a filter over a column. In many scenarios, the requirement is different: We want to remove a filter without replacing it with a new one. In these scenarios, we need REMOVEFILTERS.

A very common scenario happens when you need to compute a percentage. For example, let’s pretend you want to compute the report in Figure 3-29 that shows the percentage over the total for each brand.

FIGURE 3.29 The Pct measure computes the sales percentage of the current brand over the total.

Computing the percentage in itself is not a big deal: It is enough to divide the Sales Amount of the current cell by the Sales Amount at the grand total level and then format the result as a percentage. Computing the sales amount of the current cell is simple because the filter context makes Sales Amount compute the sales of the current brand in each cell. However, to compute the denominator, we must remove the filter on Product[Brand] to compute the total of more than 12 million.

REMOVEFILTERS does exactly this: It removes any filter from a column or a table. Here is the code of Pct:

Measure in the Sales table

Pct =
DIVIDE (
    [Sales Amount],
    CALCULATE (
        [Sales Amount],
        REMOVEFILTERS ( 'Product'[Brand] )
    )
)

In Figure 3-30, you can observe the flow of the code execution at the denominator level.

FIGURE 3.30 At the denominator level, the filter context no longer has any filter on the Brand because of REMOVEFILTERS.

Note that the previous formula only removes the filter from Product[Brand]. If the cell filter context filtered other columns, they would be untouched by REMOVEFILTERS. For this reason, the same formula can be used in a matrix that shows—for each product color—the percentage of one brand over all the brands for the same product color, as shown in Figure 3-31.

FIGURE 3.31 The filter on Product[Color] is not removed by REMOVEFILTERS ( Product[Brand] ).

REMOVEFILTERS can remove filters from one column, from multiple columns (just add them as further arguments), from an entire table, or it can also remove all the filters from the filter context. All these possibilities are handy in different scenarios.

REMOVEFILTERS is extremely helpful. However, before using it freely in your code, we suggest you wait for the end of the next chapter. REMOVEFILTERS in itself is not complex. What is complex is the manipulation of the filter context—at least when done right. In Chapter 4, “Manipulating the filter context,” we provide several examples of filter context manipulation, including the use of REMOVEFILTERS.