The KEEPFILTERS function allows modifying the behavior of the CALCULATE and CALCULATETABLE functions so that the referenced filters do not replace the existing ones in the current context, but are added to it.
KEEPFILTERS(
expression
)
- expression: Expression that defines the filter to apply.
The KEEPFILTERS function returns a table of values that will be interpreted as a filter to add to the current context.
The KEEPFILTERS function is used as an argument to the CALCULATE and CALCULATETABLE functions to modify their default behavior: Normally, a filter added as an argument to these functions modifies the filter context by being added -if the referenced column was not already considered in the filter context- or substituting part of the filter context -if the referenced column was already being considered-.
KEEPFILTERS, oppositely, adds the indicated filter without modifying the existing filter context. This means that if in the current context, for example, the condition Country = "Spain" is being imposed and the condition Country = "France" is added by the KEEPFILTERS function, both conditions will be considered in the filter context (that is, only records that satisfy both conditions will be included in the calculation).
Suppose we have a table with sales ("Movements") and a table of geographic locations associated with each sale ("Geography"). The "Units sold" measure adds the Units field from the Movements table (adding the number of units sold):
Units sold = SUM(Movements [Units])
If we take the Country field of the Geography table and the measure created to a table type display, we see the breakdown of units sold by country:
If we define a Units sold in Spain measure that, using the CALCULATE function, imposes the condition that Country = "Spain" and we take it to the previous visualization, we have the expected result:
Units sold in Spain =
CALCULATE(
[Units sold],
Geography[Country] = "Spain"
)
...returning in all calculations the number of units sold in Spain.
If, on the other hand, we replicate the previous measure by adding (not substituting) the aforementioned filter of Country = "Spain" , the result is quite different:
Units sold only in Spain =
CALCULATE(
[Units sold],
KEEPFILTERS(Geography[Country] = "Spain")
)
We see that, as a result of the addition of the new filter, only the records associated with the two countries (the one involved in the current context and "Spain") are considered in the calculation. In this example, each sale is associated with a single country, so only the sales for Spain are shown.
