Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
sjrrkb123
Helper III
Helper III

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

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.

1 ACCEPTED SOLUTION
v-stephen-msft
Community Support
Community Support

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:

v2-b2ffefc353deed044bfc30c084861260_720w.jpg

 

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.

View solution in original post

5 REPLIES 5
v-stephen-msft
Community Support
Community Support

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:

v2-b2ffefc353deed044bfc30c084861260_720w.jpg

 

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.

@v-stephen-msft  thank you I appreciate it

Hi @sjrrkb123 ,

 

Very happy to help you.

 

Best Regards,

Stephen Tao

Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
vivran22
Community Champion
Community Champion

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

 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.