Introducing the filter context and CALCULATE
- By Alberto Ferrari and Marco Russo
- 2/25/2026
Introducing KEEPFILTERS
It is interesting to observe what happens when there is a conflict between the cell filter context and the CALCULATE filter context. If we use the Product[Color] column on the rows, rather than brand and category, the result shows the same value (sales of red products) on all the rows, as shown in Figure 3-18.
){kind=link}
FIGURE 3.18 The Red Sales measure always returns the same number, irrespective of the color in the row.
This time, the filter context created by CALCULATE conflicts with the filter context created by the cell. In such scenarios, the inner filter context created by CALCULATE overrides the outer filter, thus replacing the filter on Gold with a filter on Red. As a rule, when a conflict exists, the inner filter context overrides the outer filter context.
This is the standard behavior of CALCULATE. When CALCULATE places a filter on a column that already had filters set by previous functions (or by the Power BI visual), the filter placed by CALCULATE replaces the previous filter, as shown in Figure 3-19.
)
FIGURE 3.19 The inner filter context set by CALCULATE overwrites the outer filter.
There are no special reasons for this behavior. It is what it is. DAX could have had a different semantics where the filters are merged rather than a mere replacement. However, one option had to be chosen, and the choice was filter overwriting.
This behavior can be changed by using KEEPFILTERS. KEEPFILTERS is a CALCULATE modifier that changes how CALCULATE uses the new filters to generate the inner filter context. When used, KEEPFILTERS instructs CALCULATE not to remove the outer filter but to maintain it together with the new filter. Look at the code of the measure, this time with KEEPFILTERS:
Measure in the Sales table
Once used in the matrix, it shows the value for Red only when Red is already selected in the matrix. In all other cases, it shows a blank.
In Figure 3-20, you can see the process executed for the row containing Gold, which shows a blank.
)
FIGURE 3.20 Using KEEPFILTERS keeps the outer filter inside the inner CALCULATE.
As you can see in Figure 3-20, the filter for Gold is maintained inside the formula filter context. Therefore, two filters are active simultaneously: one for Red and one for Gold. The intersection of the two filters is empty, generating a blank result.
KEEPFILTERS keeps the outer filter context inside the inner filter context. In other words, the code being executed is equivalent to this:
The two filters working together produce an empty result. The only scenario where a number is produced is when both filters (the inner and the outer) filter Red, as shown in Figure 3-21.
)
FIGURE 3.21 When both the inner and outer filters are Red, the measure computes a non-blank result.
Be mindful that both versions of Red Sales (the one using KEEPFILTERS and the one without KEEPFILTERS) are correct. It all depends on the calculation you want to obtain. In some scenarios, you want to remove the outer filter and replace it with a new one. In other scenarios, you want the opposite behavior.
Let’s elaborate further on KEEPFILTERS with another example. We author the Trendy Sales measure that computes the sales of trendy colors, which are Blue, White, or Red. The code is quite simple:
Measure in the Sales table
The measure works fine if no filter exists for Product[Color]. However, when used in conjunction with a slicer over the color, it shows a somewhat unexpected result, as shown in Figure 3-22. The sales of trendy products (Trendy Sales) are greater than those filtered by the selected colors (Sales Amount).
)
FIGURE 3.22 The filter replacement produced by CALCULATE makes numbers hard to understand.
What happens is that the inner filter on Blue, White, and Red replaces the outer filter on Azure, Blue, Gold, and Orange. In other words, the measure is computing sales for colors that—according to the slicer—should not be part of the calculation.
Even though the problem is more evident with the Adventure Works brand—because Trendy Sales is larger than Sales Amount, which is nonsensical—the same problem exists for any brand. In the following examples, we focus on “The Phone Company” rather than the “Adventure Works” brand because data distribution makes it easier to understand what happens with the filter context.
In Figure 3-23, you can see how the resulting filter context is computed in the Trendy Sales measure for “The Phone Company” brand.
)
FIGURE 3.23 The filter context for the trendy colors replaces the cell filter context.
Again, this might be what you want to compute: a different set of colors that ignores the Color slicer, resulting in a larger amount for Adventure Works and a smaller amount for The Phone Company. However, you most likely want to abide by the slicer filters and add the trendy colors as an additional filter. In that case, KEEPFILTERS is the way to go:
Measure in the Sales table
In Figure 3-24, you can see that by using this version of Trendy Sales, both filters on color are active at the same time in the resulting filter context. The intersection between the filter coming from the slicer and the additional filter created by CALCULATE makes only Blue visible through this filter context because it is the only color that exists in both filters.
)
FIGURE 3.24 When both filters over Product[Color] are active in the resulting filter context, only Blue remains visible.
Figure 3-25 shows the result of the Trendy Sales version that uses KEEPFILTERS. The cell for The Phone Company shows an empty value because only blue products are visible in the resulting filter context, and The Phone Company has no Blue products. The cell for Adventure Works shows the same value for Sales Amount and Trendy Sales because all the selected sales are of Blue products.
)
FIGURE 3.25 By using KEEPFILTERS, the cell for The Phone Company now shows a blank value and Adventure Works now shows the same value as Sales Amount—which is correct.