KEEPFILTERS DAX Function in Power BI

The KEEPFILTER DAX function is a filter modifier, which modifies how filters are applied while evaluating a CALCULATE or CALCULATETABLE function.

The KEEPFILTERS function in DAX (Data Analysis Expressions) is used to preserve the existing filters on a column or table in our calculations, even when those filters might otherwise be overridden by the context of the calculation.

This function is particularly useful in situations where we want to enforce specific filters within a formula, regardless of the broader filter context.

DAX Syntax KEEPFILTERS(expression)
• expression: A table expression that returns a table of values to be filtered.

How it Works • When we apply KEEPFILTERS to a filter argument in functions like CALCULATE, it ensures that existing filters on the specified columns or tables are preserved and combined with the new filters being applied.
• Without KEEPFILTERS, the new filters would replace the existing ones.

Example: Let’s create a measure without KEEPFILTERS.

DAX

If there is an existing filter on Sheet1[Country], it will be overridden by the filter Sheet1[Country] = "Australia".

We have seen that the Country column is ignored from the filtering context. Because the default behavior of CALCULATE function is to override the existing filter context (including the slicer, report, page and visual filters). But if we want to use that in the filtering context then we can use the KEEPFILTERS function.

Let’s create another measure with KEEPFILTERS.

DAX

In this case, the existing filters on Sheet1[Country] will be combined with the filter Sheet1[Country] = "Australia".

Here all the filter context and the conditional filter in the calculation both are applied when we are using KEEPFILTERS function. So, it is used to preserve filters rather than overwrite them.

Let’s add the two measures created above on the card visuals as shown below:

If we have applied the page level filter on the Country column and not include the country value Australia. Then the measure includes the KEEPFILTERS returns the result Blank, but even though the measure without KEEPFILTERS returns the results, as CALCULATE function by default removes any filter context and replaced or override it with the given conditional filter.

Key Scenarios of using KEEPFILTERS DAX function 1. Combining Filters: When we want to add filters without removing the ones already applied. When combined with other filters, the result is an AND operation.
2. Preserving Context: Ensuring that specific filters in a report page or visual stay active while performing calculations.