- KEEPFILTERS is a CALCULATE modifier
that changes the way CALCULATE applies filter arguments
to the new filter context that is being created.
By default, CALCULATE overrides the outer filter context
and to do that, it removes the outer filter context
on the column that is being filtered
and replaces it with the new filter context.
By using KEEPFILTERS,
CALCULATE will keep the outer filter
and add the new filter as a further filter.
Let's see a couple of examples where it proves to be useful.
In this first example,
we have a MEASURE white sales
that computes sales amount
and uses product color equals white without KEEPFILTERS.
The result is that the value of white sales
is shown everywhere.
Even though we have a filter by color silver
or black or white,
this condition overrides the outer filter context
and always shows the white sales.
By using KEEPFILTERS product color equals white,
CALCULATE will not remove the outer filter.
It will keep the outer filter
and add the new filter as a second filter.
Therefore, in this row,
we will have one filter for silver
that is still active because of KEEPFILTERS,
plus a new filter, product color equals white.
White and silver, they clash with each other,
therefore the result is a blank.
The only point where you have a value
is when white is already in the filter context.
KEEPFILTERS can be used as a CALCULATE modifier,
so inside CALCULATE,
but there is also another usage of KEEPFILTERS,
which is as a modifier for the first argument
of an iterator like SUMX.
In this case, KEEPFILTERS will change
the way context transition happens during the iteration.
Its usage is kind of complex
but in this example, I will try to explain you the details
of why and when it is useful.
If you create a measure sales x
that just iterates over the values of product color
and computes the sales amount,
this measure is relying on context transition happening here
to compute the sum of the sales amount
for all the colors.
You can also write the same expression using KEEPFILTERS
VALUES of product color.
In a normal behavior, in a normal report,
the two measures would return exactly the same value.
But you might notice a difference
if you're filtering using an arbitrarily shaped set.
If you create a filter that contains 2008 red
and 2007 white, what I'm doing here
is I'm creating a table
that contains one column for the year
and one column for the color.
The TREATAS then changes in the year and the color
so that this table actively filters the date
and the product table.
Now, when context transition happens,
if this filter is active,
context transition will replace the filter on the color.
By doing that, it removes both red
and white from the filter
but what remains in the filter
is basically 2008, 2009.
So by replacing the outer filter,
CALCULATE destroys the nature
of the arbitrarily shaped set
and creates a filter containing two years
plus one filter containing one color.
Therefore, the result will be wrong.
By using KEEPFILTERS, on the other hand,
the outer filter, which is arbitrarily shaped set
is kept, and the new filter is added.
You can see the result here.
Sales amount shows this value.
Sales x, because of the way we wrote it,
should return the same number.
Instead, it does not.
It shows a different value
because this is computing the sales in 2008 and 2007,
which are white.
Similarly here for red,
we are computing the sales in 2008 and 2007,
which are red, providing the wrong result.
Using KEEPFILTERS protects this code
from being executed also in the unlikely scenario
where you have an arbitrarily shaped set
that is actively filtering your calculation.