If you ever spend time looking at DAX generated by the Power BI Desktop Performance Analyser, you may notice a function called KEEPFILTERS appear from time to time. It’s not a function I find myself using often, so I thought I’d do a bit of digging to find out what it does and when it might be useful.
The short answer is, the KEEPFILTERS function allows you to control which filters get applied to a calculation. The function plays a role in determining which rows should, or should not be considered by the calculation, in the same way, a WHERE clause impacts a T-SQL statement.
Consider the following data-set along with a requirement that we want a measure that only shows the value of 11 (Sum of the VALUE column), and only on the line where the ID is A.
The model only contains a single measure using the following definition and can be downloaded here.
Measure = SUM([Value])
Before we start, let’s remind ourselves about a couple of important fundamentals on how filters get applied in DAX.
Predicate criteria get re-written internally
Functions such as CALCULATE and CALCULATETABLE allow you to pass filter instructions in the form of a table. Using a table as a filter will enable you to build a simple (or complex) DAX expression to determine the required filtering, so long as the DAX expression returns a table of data.
In many cases, the filter requirement is simple, so DAX allows you to write a simple expression that doesn’t return a table – but then this gets automatically re-written to a DAX expression that does.
CALCULATE(
[Measure] ,
'Table'[ID] = "C"
)
In the above statement, the simple instruction in line 3 does not return a table. However, when run, the predicate is re-written using functions that do return a table. The result of both expressions will always be the same.
CALCULATE(
[Measure] ,
FILTER(
ALL('Table'[ID]),
'Table'[ID] = "C"
)
)
It is important to note when DAX re-writes the expression using a short-form predicate to use FILTER; it uses the ALL function. The ALL function in DAX is pretty powerful and clears all outer filters over the ‘Table'[ID] column that could apply from slicers etc.
In some cases, the ALL function provides the desired result, however when it doesn’t, the KEEPFILTERS function may just be the help you need.
Inner Filters override Outer filters.
Consider the following nested set of three CALCULATE functions. Each CALCULATE function applies a different predicate over the same ‘Table'[ID] column. At first glance, you might think the various filter clauses are all in conflict with each other, and the measure should never return a value. If all the predicates are applied equally, it is not possible to meet all three criteria.
CALCULATE(
CALCULATE(
CALCULATE(
[Measure] ,
'Table'[ID] = "C"
),
'Table'[ID] = "B"
),
'Table'[ID] = "A"
)
However, DAX applies filter logic in layers starting from the outside. In this example, the first layer of filtering applied comes from the CALCULATE statement in line 1, which specifies the ‘Table'[ID] column should = “A”.
Then, the second layering of filters gets applied by the inner CALCULATE function in line 2. This new layer specifies a filter over the ‘Table'[ID] column to only consider rows with a value of “B” in the column.
As covered earlier in the article, the short form filter notation used here gets automatically re-written to the extended version using both the FILTER and ALL functions. The ALL function used by the inner layer clears any previous filtering on the ‘Table'[ID] column and now applies the “B”.
CALCULATE(
CALCULATE(
CALCULATE(
[Measure] ,
FILTER(ALL('Table'[ID]), 'Table'[ID] = "C"
),
FILTER(ALL('Table'[ID]), 'Table'[ID] = "B"
),
FILTER(ALL('Table'[ID]), 'Table'[ID] = "A"
)
Finally, the innermost CALCULATE statement at line 3 gets applied last. The original calculation uses the short-form predicate that ‘Table'[ID] = “C”. This format gets re-written to use both FILTER and ALL functions that clear all previous filters on the ‘Table'[ID] column.
A measure using three layers of nested CALCULATE functions such as this will return the following result – which happens to match the SUM of all the values in the ‘Table'[Value] column which have “C” in the ‘Table'[ID] column.
If you are wondering why we see the value of 31 repeated in the table visual, even in rows where the ID column doesn’t match, that’s because of the same overriding principle. If you look at the DAX produced by Power BI Desktop to generate the data it needs for the table visual, it looks something like the following expression.
// DAX Query
EVALUATE
SUMMARIZECOLUMNS(
'Table'[ID],
"Nested_Calculation", 'Table'[Nested Calculation]
)
The nested calculation at line 4 is the same measure using three layers of CALCULATE to apply three layers of filtering over the ‘Table'[ID] column. In this case, the first parameter of SUMMARIZECOLUMNS is the ‘Table'[ID] column which returns the values gets used in the first column of the visual. Grouping on the ‘Table'[ID] column automatically applies an outer layer of filtering on this column which happens to be overridden three times in our case.
The Practical
To see another example of how the ALL function is impacting results, Here are two measures using the short and expanded versions of the filter logic.
No Keepfilters with ALL (Short) =
CALCULATE(
[Measure],
'Table'[ID]="A"
)
No Keepfilters with ALL (long) =
CALCULATE(
[Measure],
FILTER(
ALL('Table'[ID]),
'Table'[ID]="A"
)
)
When both measures get added to a table visual in Power BI Desktop, we get the following result, which is the same value appears in both columns against every [ID] from the first column.
If this is the desired result, we can stop here. However, if the desired result is only to show the value of 11 on the top row, we need to alter the DAX..
The updated DAX can’t use the short-form predicate because we want to avoid using the ALL function to clear existing filters over the ‘Table'[ID] column.
The new calculation below looks very similar to the expanded version if we had used the short-form notation. The critical difference is the use of the VALUES function in place of the ALL function. The difference in behavior is the VALUES function does not clear outer layers of filters over the ‘Table'[ID column.
No Keeps Filters with VALUES (long) =
CALCULATE(
[Measure],
FILTER(
VALUES('Table'[ID]),
'Table'[ID]="A"
)
)
The logic inside the calculation does not override the filtering from using ‘Table’ [ID] as the first column in the table visual. The effect of this means most of the rows in the final column have conflicting filter logic except where the outer and inner filters align.
| Outer Filter | Inner Filter | Rows from ‘Table’ | |
| Row 1 | ID = A | ID = A | 1 row |
| Row 2 | ID = B | ID = A | 0 rows |
| Row 3 | ID = C | ID = A | 0 rows |
| Row 4 | ID = D | ID = A | 0 rows |
So, far so good. We have our desired result but haven’t used the KEEPFILTERS function as yet. The KEEPFILTERS function allows us to use the short-form notation of ‘Table[ID] = “A” but modifies the behaviour of the ALL function to keep filters over the [ID] column rather than clear them.
How would this look? If we wrap the filter predicate with the KEEPFILTERS function as follows. The short-form predicate at line 4 gets internally expanded and rewritten to use FILTER and ALL, however, the KEEPFILTERS function prevents the ALL function from overriding the outer filter coming from the first column.
Keeps Filters with ALL (short) =
CALCULATE(
[Measure],
KEEPFILTERS('Table'[ID]="A")
)
Summary
In summary, it’s always helpful to understand the layered nature to how filters get applied in DAX calculations and expressions. The language also provides mechanisms that allow you to adjust and tailor filters to suit your requirement.
It’s also helpful to understand what happens when you include a short-form version of a filter predicate, which I think I have hopefully covered here.
As always, please let me know if you have questions, or if you found this explanation helpful.
Some additional reading on KEEPFILTERS is here :
https://www.sqlbi.com/articles/using-keepfilters-in-dax/