KEEPFILTERS Function in DAX

Each time I reply to a comment with the DAX Code that has a KEEPFILTERS function in it, I inevitably get the response..
“Why did you use the KEEPFILTERS function, what does it do?”

This post is dedicated to all folks who asked me about KEEPFILTERS, and didn’t respond adequately.

Grab the Video!

DAX KEEPFILTERS Function

If I have to explain KEEPFILTERS in the simplest way possible, I’ll say this

KEEPFILTERS function adds the external filters to the current context

Now obviously this is my definition and I am particularly interested in explaining 2 things..

• What do you mean by adding filters to the current context ?
• And what are “External Filters” ?

Before I go on perusing the KEEPFILTERS function, you must understand these 2 things

1. KEEPFILTERS results in a Table.
2. KEEPFILTERS can be used as both, the filter argument with the CALCULATE / CALCULATETABLE functions and as a Table Function too.

Using KEEPFILTERS with CALCULATE

Consider this simple Pivot table with the Measure Total Sales displayed across Product Color (from the Products Table)

Let’s add another Measure to this Pivot Table

Black Sales = 
CALCULATE(
    [Total Sales],
    Products[Color] = 'Black'
)

If you understand the behavior of the CALCULATE function you’d know the above measure will override all the other colors with “Black”.

See these results

Sometimes you don’t want this behavior from CALCULATE i.e NOT overriding other colors with Black Sales. In that case you can wrap the CALCULATE filter in the KEEPFILTERS function.

Black Sales with KEEPFILTERS = 
CALCULATE(
    SUM(Sales[SalesAmount]),
    KEEPFILTERS(Products[Color] = 'Black')
)

How does the KEEPFILTER function work?

Recall my definition – KEEPFILTERS function adds the external filters to the current context. Let me explain and the definition will make sense to you.

Focus on the Filter Context Product[Color] = Red and the Blank value against it.

1. I write a vlookup / RELATED function to pull the color from the Products Table to the Sales Table
2. I then apply 2 filters
   • Filter 1 – Comes from the CALCULATE function. Color = Black
   • Filter 2 – Comes from the current context. Color = Red
3. End result is a blank table therefore the Measure returns a blank.

Notice the behavior of KEEPFILTERS function, it added the external filters (from the Pivot Table i.e. Color = Red) to the filters in the CALCULATE function (Color = Black). Thus producing the result we need.

KEEPFILTERS allows the visual filters to add up on top of CALCULATE Filters.

Using KEEPFILTERS as a Table

You can also use the KEEPFILTERS as a Table Function. Needless to say the behavior will remain the same.

Consider this measure and it’s results..

KEEPFILTERS Table Wrapper = 
SUMX(
    ALL(Products[Color]),
    [Total Sales]
)

1. ALL ignores the Color Filter and creates a list of all available colors.
2. The SUMX iterates through each one of them and sums up all Sales Values.
3. Thus you get the Total is displayed throughout the table.

Let’s tweak this measure

KEEPFILTERS Table Wrapper = 
SUMX(
    KEEPFILTERS(ALL(Products[Color])),
    [Total Sales]
)

1. KEEPFILTERS add the filters coming from the Pivot Table to the ALL Function and you are left with only a single color in the context.
2. Thus returning the individual color Sales value.

More on KEEPFILTERS

1. SQL BI on KEEPFILTERS
2. Phil Seamark on KEEPFILTERS
3. Microsoft Documentation on KEEPFILTERS

More on DAX

1. EARLIER Function
2. ALLSELECTED Function
3. CALCULATE Function
4. CALCULATE trick for Non Blank Values
5. Slab / Tier Based Calculations in Power BI