KEEPFILTERS DAX Function (Filter)

Changes the CALCULATE and CALCULATETABLE function filtering semantics.

Syntax

KEEPFILTERS ( <Expression> )
Parameter Attributes Description
Expression

CALCULATE or CALCULATETABLE function expression or filter.

Remarks

KEEPFILTERS is a filter modifier that does not remove an existing column or table filter in the filter context that conflicts with the filter applied by the argument of KEEPFILTERS used as:

  • a filter argument in CALCULATE / CALCULATETABLE
  • an argument of an iterator used in a following context transition

Examples

In the following example, KEEPFILTERS is used as a filter modifier in CALCULATE. The Always Red Sales measure returns always the sum of the Red products, overriding any existing filter over the Color column. The measure Only Red Sales return the sales of red products within the set of selected colors. If Red is not included in the current filter context, the result is blank.

Always Red Sales :=
CALCULATE (
    [Sales Amount],
    'Product'[Color] = "Red"
)

Only Red Sales :=
CALCULATE (
    [Sales Amount],
    KEEPFILTERS ( 'Product'[Color] = "Red" )
)

When KEEPFILTERS is used for an iterator, it keeps the existing filter context when there is a context transition. The measure Average Sales Only Trendy Colors computes the average Sales of the colors included within the Trendy Colors, without considering those that are not in the current filter context. If the measure is evaluated in a filter context that has a filter over Red, Yellow, and White, the result will average only Red and White, ignoring Yellow and Blue colors.

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

Related articles

Learn more about KEEPFILTERS in the following articles:

  • Using KEEPFILTERS in DAX

    This article explains how to use KEEPFILTERS to intersect instead of overriding an existing filter context in DAX, simplifying the code and improving performance. » Read more

  • Filter Arguments in CALCULATE

    A filter argument in CALCULATE is always an iterator. Finding the right granularity for it is important to control the result and the performance. This article describes the options available to create complex filters in DAX. » Read more

Related functions

Other related functions are:

Last update: Dec 14, 2018   » Contribute   » Show contributors

Contributors: Alberto Ferrari, Marco Russo, Jes Hansen

MSDN documentation: https://msdn.microsoft.com/en-us/query-bi/dax/keepfilters-function-dax

DAX Conventions
Context Transition

This function performs a Context Transition if called in a Row Context. Click to read more.

Row Context

This expression is executed in a Row Context. Click to read more.

Iterator

Not recommended

The use of this function is not recommended. See Remarks and Related functions for alternatives.

Not recommended

The use of this parameter is not recommended.

Deprecated

This function is deprecated. Jump to the Alternatives section to see the function to use.

Deprecated

This parameter is deprecated and its use is not recommended.

Contribute

Want to improve the content of KEEPFILTERS? Did you find any issue? Please, report it us!
All submissions will be evaluated for possible updates of the content.