Thinking Behind Use of KEEPFILTERS

[AntrikshSharma]

In this article I want to show you how you can improve the performances of your queries by using KEEPFILTERS.

Here is the snapshot of the data model

Here are few straight forward reasons before I explain why I use it so often.

1. KEEPFILTERS creates a SET intersection between what is written in the code and what is available in filter context outside CALCULATE i.e. slicers, rows, columns
2. Makes it easier to write predicate/boolean conditions without overwriting the existing filter context. The end result is more readable and elegant looking
3. By using KF you are able generate more efficient queries with column filters, since now you don't have to iterate a full dimension or fact table
4. Writing predicate ensures you only get unique existing combinations and KEEPFILTERS ensures that filters inside CALCULATE and outside CALCULATE intersect

Predicate statement =
CALCULATE (
    [Total Sales],
    KEEPFILTERS ( Products[Color] IN { "Red", "Green", "Blue" } )
)

Non predicate equivalent =
CALCULATE (
    [Total Sales],
    FILTER ( Products, Products[Color] IN { "Red", "Green", "Blue" } )
)

Not used very commonly but you can use KEEPFILTERS with iterators too, in that case it creates an intersection between context transition and the existing filters.

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

I am going to use Contoso dataset with 12.5 Million rows for the demonstration.

Let's say you want to create a report showing sales only for trendy colors otherwise blank.

you would want to write the measure in the following way so that you want the sales of the colors that are trendy plus included in the slicer

Trendy colors =
CALCULATE (
    [Total Sales],
    FILTER ( Products, Products[Color] IN { "Red", "Green", "Blue" } )
)

So far everything is fine no issues. Now lets see the query generated by this measure.

Result:

Pay attention to the number of Rows this measure had to iterate, because we used a full table inside CALCULATE, a full scan is also done to retrieve the values.

If on the other hand I modify the measure a little bit by introducing KEEPFILTERS, look at the query generated and the result is same too!

Result:

Moving on to a more complex example. Now we are trying to calculate sales amount where quantity * net price is greater than 1000.

Some might write the code like this:

Sales Amount GT 1000 =
CALCULATE (
    [Total Sales],
    FILTER (
        Sales,
        Sales[Quantity] * Sales[Net Price] > 1000
    )
)

This works fine and you can interact with slicer and obtain the result depending on the quantity you select.

And same can be done with the following, look at the ALL statement it will contain unique combination of Quantity and Net price and once the product is greater that 1000 only the values of these 2 columns would be applied to the filter context.

Sales Amount GT 1000 KF =
CALCULATE (
    [Total Sales],
    KEEPFILTERS (
        FILTER (
            ALL (
                Sales[Quantity],
                Sales[Net Price]
            ),
            Sales[Quantity] * Sales[Net Price] > 1000
        )
    )
)

In case of full table all the columns of the sales would be applied to the filter context and that could be very expensive in case there are a lot of columns, and to be honest I don't think you would need every column of a table to get the result. And the number of rows applied to the filter context are huge too!  (I say all the columns will be applied to the filter context, in this scenario the code is so simple that the engine know not to iterate all the columns, but in cases with nested measures or complex expressions, all the columns can be materialized)

Let's pay attention to the queries generated by these 2

without KEEPFILTERS query:

With KEEPFITLERS query:

By now you can see how many rows the SE engine has to bring back to get the desired result.

Another example:

Let's say you are slicing trendy colors by brands.

Measures Used:

Trendy colors =
DIVIDE (
    CALCULATE (
        [Total Sales],
        FILTER ( Products, Products[Color] IN { "Red", "Green", "Blue" } )
    ),
    CALCULATE ( [Total Sales], ALL ( Sales ) )
)

Trendy Color without KF =
DIVIDE (
    CALCULATE ( [Total Sales], Products[Color] IN { "Red", "Green", "Blue" } ),
    CALCULATE ( [Total Sales], ALL ( Sales ) )
)

Trendy Color with KF =
DIVIDE (
    CALCULATE (
        [Total Sales],
        KEEPFILTERS ( Products[Color] IN { "Red", "Green", "Blue" } )
    ),
    CALCULATE ( [Total Sales], ALL ( Sales ) )
)

But if you change the field to Colors, the difference is clearly visible:

That's why I use KEEPFILTERS more often as it helps in creating elegant and efficient code But knowing when to use it is absolutely necessary.

The insipiration for the Measures came from The Definitive Guide to DAX by Marco Russo and Alberto Ferrari, so kudos to these two legends for teaching us newbies everything there is to know about DAX.