KEEPFILTERS Function (DAX)

How does the KEEPFILTERS function (DAX) work?

The KEEPFILTERS function (DAX) modifies the way filters are applied when evaluating a CALCULATE or CALCULATETABLE function.

KEEPFILTERS Formula Syntax

KEEPFILTERS(
     <number>
)

How do you use the KEEPFILTERS function?

You use KEEPFILTERS to override the standard behavior of these functions in the context of CALCULATE and CALCULATETABLE functions.

By default filter arguments s are used as the background for evaluating the expression in functions such as CALCULATE, and as such filter arguments for CALCULATE replace all existing filters over the same columns. The new context performed by the CALCULATE filter argument only affects existing filters on the columns listed as part of the filter argument. Filters on columns other than those listed in the CALCULATE arguments or other related functions remain in effect and unchanged.

The KEEPFILTERS function allows you to modify this behavior. When using KEEPFILTERS, any existing filters are compared with the columns in the filter arguments in the current context, and the intersection of those arguments is used as the context for evaluating the expression. The net effect over any single column is that both sets of arguments apply: the filter arguments used in CALCULATE and the filters used in the KEEPFILTER function arguments. In other words, while the current context is replaced by CALCULATE filters, KEEPFILTERS adds filters to the current context.

The numbers returned from the expressions used as parameters can be returned as positive numbers using the nested KEEPFILTERS function.

Related Blog Posts

Related Support Forum Posts

Use of KEEPFILTERS Function
Modeling increase by group and year
Divide with Filter Context

Considerations when using the KEEPFILTERS function

Although KEEPFILTERS might be considered a table function, it changes the behavior of a predicate or table expression provided as an argument as a filter modificator. Therefore a function that returns a value can not be considered.

Related Video Tutorials

Formula examples using the KEEPFILTERS function

AlwaysRed_Filter :=
CALCULATE (
      [Sales Amount],
      FILTER (
         ALL ( Products[Color] ),
      Products[Color] = “Red”
      )
)

Average Sales Only Trendy Colors :=
VAR TrendyColors =
      TREATAS (
           { “Red”, “Blue”, “White” },
          ‘Product'[Color]
      )
RETURN
      AVERAGEX (
          KEEPFILTERS ( TrendyColors ),
           [Sales Amount]
      )

Related DAX Functions

calculatetable

Related Course Modules

Ultimate-Beginners-Guide-To-PowerBI
Center of Excellence

DOWNLOAD


Download our comprehensive DAX Formula Reference Guide as perfect companion as you learn how to use DAX formulas within Power BI.


Download DAX Formulas Reference Guide
Download DAX Formula Reference Guide
Membership