Uncovering the secrets of KEPFILTERS in DAX | by Salvatore Cagliari | Jul, 2023


The KEEPFILTERS() function in DAX is an underestimated function. So, I decided to go into the rabbit hole of this function and provide you with some interesting details about this function and one surprising effect.

Photo by Ian Tuck on Unsplash

When we use the CALCULATE() function in DAX, we usually add a simple filter like this:

Product[Color] = “Green”

This filter replaces any existing filter on the column [Color] with the value “Green”.

But sometimes, we need to go the extra step and preserve an existing filter on a table or a column to perform some interesting calculations.

And sometimes, we get the wrong result from our Measures, and we can’t understand why this happens.

These are the situations where the KEEPFILTERS() function can help us.

First, let’s define the Query on which we want to work.

I want to get a List of Online Sales by Color:

DEFINE
MEASURE 'All Measures'[Online Sales] = SUMX('Online Sales', [UnitPrice]*[SalesQuantity])

EVALUATE
SUMMARIZECOLUMNS('Product'[Color]
,"Online Sales", [Online Sales]
)

I use SUMX to multiply [UnitPrice] by [SalesQuantity].

The result looks like this:

The query looks like this when I add a filter with CALCULATE(), as described above.

// Only Green Sales
DEFINE
MEASURE 'All Measures'[Online Sales] = SUMX('Online Sales', [UnitPrice]*[SalesQuantity])

MEASURE 'All Measures'[All Green Sales] =
CALCULATE([Online Sales]
,'Product'[Color] = "Green"
)

EVALUATE
SUMMARIZECOLUMNS('Product'[Color]
,"Online Sales", [Online Sales]
,"Green Sales", [All Green Sales]
)

And the result looks like this:



Source link

Leave a Comment