Thinking Behind Use of KEEPFILTERS in DAX

In this blog let’s understand why you should use KEEPFITLERS more often.

Below is the snapshot of the data model ( Contoso Retail Data Warehouse with 12.5 Million rows )

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

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.

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

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:

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.

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!

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 :

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.