1. Diff between Filter() and KeepFilters()

[Anonymous]

Hi Team,

Can you please explain me the exact differences between Filter() and KeepFilters().

Pease explain me by taking the scenario. If possible pls add the Pbix / screenshots of the result.

Please don't post me the definition.

Thanks,

Masthan

[v-yalanwu-msft · Accepted solution]

Hi, @Anonymous

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.

[Greg_Deckler]

@Anonymous Depends a bit on the exact scenario but let's say you are using CALCULATE to do a simple SUM. You would use FILTER to add a filter to the sum. So, let's say you have some filters in your Filters pane. Those filters affect your sum by removing rows in context. You would add a filter using FILTER that adds an additional filter that removes additional rows.

You would use KEEPFILTERS to remove all filters in context except the ones that you specify in your KEEPFILTERS statement.