Conceptual Question - FILTER vs KEEPFILTERS vs neither - What is the difference?

[sjrrkb123]

As I have been working with Power BI more and more, I have been wondering what is the difference in the following calculation:
1) "Neither"
Calculate(SUM('Table 1'[Profit]), 'Customer'[Name] = "Bob"))

2) FILTER
Calculate(SUM('Table 1'[Profit]), FILTER('Customer', 'Customer'[Name] = "Bob"))

3) KEEPFILTERS 
Calculate(SUM('Table 1'[Profit]), KEEPFILTERS('Customer'[Name] = "Bob"))

So far what I do know from reading the documentation is the FILTER and KEEPFILTERS differ in what engine they use. KEEPFILTER uses the storage engine and cannot be used in comparing columns to columns (or columns to measures). The FILTER argument uses the formula engine and can do everything KEEPFILTERS does and much more advanced comparisons. 

What I am confused about is often, all three result in the same answer. 

So my question is this: What circumstances allow (1), (2), & (3) options to result in the same answer and when would the provide different results?

Thank you for any assistance provided.

[v-stephen-msft · Accepted solution]

Hi @sjrrkb123 ,

KEEPFILTERS is a filter modifier that does not delete existing column or table filters in the filter context that conflict with the filters applied by the KEEPFILTERS parameter.

Probably still feel very abstract. Before we explain the specific usage of this function, let’s take a look at a case: Calculate the sales of products whose color is red. Most people will write the following measure:

AlwaysRed =
CALCULATE ( 
 [SalesAmount], 
 Products[Color]= "Red"
)

Inside the engine, this expression will be transformed into the following expression:

AlwaysRed_Filter =
CALCULATE (
 [SalesAmount],
 FILTER(
 ALL( Products[Color] ),
 Products[Color]= "Red"
 )
)

In other words, the ALL function inside CALCULATE removes the filtering of the Color column by the external filter, and all external filters are calculated according to the new filter color as red.

If you don't want to lose the existing external filters, a widely used method is to replace ALL with VALUES, the code is as follows:

OnlyRed_Values =
CALCULATE (
 [SalesAmount],
 FILTER(
 VALUES( Products[Color] ),
 Products[Color]= "Red"
 )
)

Let's take a look at how the results of several functions will differ, as shown in the following figure:

In the measure AlwaysRed, because of the existence of ALL, the red filter overrides the color filter of the external filter, so the result will be the number of product sales in red; and Values is used in OnlyRed_Values, which does not cancel the external context filter , The result at this time is the result of the interaction between the external context and the internal context. Taking BLACK as an example, the external context filters black, and the internal filters red. The intersection between the two is empty, so the result is 0.

For the effect achieved by OnlyRed_Values, we can also use KEEPFILTERS to achieve, first look at the code:

OnlyRed =
CALCULATE (
 [SalesAmount],
 KEEPFILTERS( Products[Color] = "Red" )
)

Correspondingly, this code is equivalent to the following code:

OnlyRed_KeepfiltersAll =
CALCULATE (
 [SalesAmount],
 KEEPFILTERS(
 FILTER(
 ALL( Products[Color] ),
 Products[Color]= "Red"
 )
 )
)

Let us re-interpret this code through the previous grammatical description of the function. ALL cancels the color filtering of the external context. When the external context conflicts with the color, the internal filter is used instead, but the appearance of KEEPFILTERS retains the external filter. Filter the color so that the same result is calculated as the measure value OnlyRed_Values.

Okay, to make a simple summary:

CALCULATE([SalesAmount],KEEPFILTERS( Products[Color] = "Red" ) )

CALCULATE([SalesAmount],KEEPFILTERS (FILTER (ALL ( Products[Color]),Products[Color] = "Red")))

CALCULATE([SalesAmount],FILTER(VALUES( Products[Color] ),Products[Color] = "Red"))

The three measure values are consistent in the calculation results. When the function of the ALL family is used as a filter inside CALCULATE, the filtering effect of the external filter will be cancelled. At this time, KEEPFILTERS will make the external context play a screening role again. In terms of data presentation effect, it is the same as using VALUES inside CALCULATE. When you think of using FILTER(VALUES(Col),Expression) to interact with external context, you can use KEEPFILTERS(<Exprission>) instead. The advantage is that the code is more concise and the execution speed is better.

[sjrrkb123]

@v-stephen-msft  thank you I appreciate it

[v-stephen-msft]

Hi @sjrrkb123 ,

Very happy to help you.

Best Regards,

Stephen Tao

[Ashish_Mathur]

Hi,

There are 2 triggers for a FILTER() function.  One is to resolve a conflict that arises when something is specified in a filter/slicer and something else needs to filtered directly in the CALCULATE() function.  The other is when you want to test for rich filter conditions (any filter condition other than comparing a column with a fixed value).  Your example is a perfect case of a simple filter (comparing a column to a fixed value) and therefore the first form is what yo need.  I do not use KEEPFILTERS() for the very reason quoted by you.  That being said, FILTER() is an iterator and therefore over large datasets, it will hamper performance.

Hope this clarifies.

[vivran22]

Hey @sjrrkb123 ,

The formula 1 & 2 will return same result. Eventually, DAX converts the following:

Calculate(SUM('Table 1'[Profit]), 'Customer'[Name] = "Bob"))

as

Calculate(SUM('Table 1'[Profit]), FILTER (ALL(Customer),'Customer'[Name] = "Bob")))

It overrides any filter applied by the visual.

whereas KEEPFILTERS retains the filter applied by the visual as well. You may refer to the following artilce for more detail:

https://www.vivran.in/post/dax-keepfilters

Cheers!
Vivek

Blog: vivran.in/my-blog
Connect on LinkedIn
Follow on Twitter