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.

Return values

Even though KEEPFILTERS could be considered a table function, as a filter modifier it changes the behavior of a predicate or of the table expression provided as an argument. Therefore, it cannot be considered a function returning a value.

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
» 9 related articles
» 2 related functions

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]
    )
-- Compare a White color filter with and without KEEPFILTERS
DEFINE 
    MEASURE Sales[White Sales] = 
        CALCULATE ( 
            [Sales Amount], 
            Product[Color] = "White" 
        )
    MEASURE Sales[White Sales Keep] = 
        CALCULATE ( 
            [Sales Amount], 
            KEEPFILTERS ( Product[Color] = "White" )
        )
EVALUATE
    ADDCOLUMNS (
        VALUES ( 'Product'[Color] ),
        "Sales Amount", [Sales Amount],
        "White Sales", [White Sales],
        "White Sales Keep", [White Sales Keep]
    )
ORDER BY [Sales Amount] DESC
Product[Color] Sales Amount White Sales White Sales Keep
Silver 6,798,560.86 5,829,599.91 (Blank)
Black 5,860,066.14 5,829,599.91 (Blank)
White 5,829,599.91 5,829,599.91 5,829,599.91
Grey 3,509,138.09 5,829,599.91 (Blank)
Blue 2,435,444.62 5,829,599.91 (Blank)
Green 1,403,184.38 5,829,599.91 (Blank)
Red 1,110,102.10 5,829,599.91 (Blank)
Brown 1,029,508.95 5,829,599.91 (Blank)
Orange 857,320.28 5,829,599.91 (Blank)
Pink 828,638.54 5,829,599.91 (Blank)
Silver Grey 371,908.92 5,829,599.91 (Blank)
Gold 361,496.01 5,829,599.91 (Blank)
Azure 97,389.89 5,829,599.91 (Blank)
Yellow 89,715.56 5,829,599.91 (Blank)
Purple 5,973.84 5,829,599.91 (Blank)
Transparent 3,295.89 5,829,599.91 (Blank)
--  KEEPFILTERS can be used as a modifier in the table 
--  arguments of iterators. In that case, it changes the 
--  way CALCULATE merges filters during context transition
--  over the iterated columns.
DEFINE
    MEASURE Sales[SalesX] =
        SUMX ( VALUES ( Product[Color] ), [Sales Amount] )
    MEASURE Sales[SalesX Keep] =
        SUMX ( KEEPFILTERS ( VALUES ( Product[Color] ) ), [Sales Amount] )
    VAR YearsAndColor =
        TREATAS (
            { ( "CY 2008", "Red" ), ( "CY 2007", "White" ) },
            'Date'[Calendar Year],
            'Product'[Color]
        )
EVALUATE
SUMMARIZECOLUMNS (
    'Product'[Color],
    YearsAndColor,
    "Sales Amount", [Sales Amount],
    "SalesX", [SalesX],
    "SalesX Keep", [SalesX Keep]
)
ORDER BY [Sales Amount] DESC
Product[Color] Sales Amount SalesX SalesX Keep
White 2,002,452.65 2,368,479.81 2,002,452.65
Red 395,277.22 2,288,307.38 395,277.22

Related articles

Learn more about KEEPFILTERS in the following articles:

Related functions

Other related functions are:

Last update: Mar 27, 2024   » Contribute   » Show contributors

Contributors: Alberto Ferrari, Marco Russo

Microsoft documentation: https://docs.microsoft.com/en-us/dax/keepfilters-function-dax

2018-2024 © SQLBI. All rights are reserved. Information coming from Microsoft documentation is property of Microsoft Corp. » Contact us   » Privacy Policy & Cookies

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.

Volatile

A volatile function may return a different result every time you call it, even if you provide the same arguments. Click to read more.

Deprecated

This parameter is deprecated and its use is not recommended.

DirectQuery compatibility

Limitations are placed on DAX expressions allowed in measures and calculated columns.
The state below shows the DirectQuery compatibility of the DAX function.

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.


This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.