CODEWITHSUNDEEP
powerbidax
karpan
karpan

Reputation: 531

when do we use FILTER and when KEEPFILTERS in CALCULATE?

I have created two measures:

Revenue Red 1 = CALCULATE([Revenue], FILTER('Product', 'Product'[Color] = "Red"))

and

Revenue Red 2 = CALCULATE([Revenue], KEEPFILTERS('Product'[Color] = "Red"))

that seem to behave similarly

enter image description here

The measure behaviour can be investigated by downloading

https://github.com/MicrosoftDocs/mslearn-dax-power-bi/raw/main/activities/Adventure%20Works%20DW%202020%20M06.pbix

and adding the two measures above.

Based on the documentation https://learn.microsoft.com/en-us/dax/keepfilters-function-dax I understand that the CALCULATE filters replace any filters on the same column, whereas the KEEPFILTERS clause always applies no matter what. But I still find this confusing and I wonder what is the best practice to use these two constructs. Any insights will be appreciated.

Upvotes: 1

Views: 3602

Answers (2)

Salomo92
Salomo92

Reputation: 11

I would like to add something to the comment posted by Marcus because I feel it doesn't answer the question 100%. As Marcus says already, this here:

Revenue Red 2 = CALCULATE([Revenue], KEEPFILTERS('Product'[Color] = "Red"))

becomes this:

Revenue Red 2 = CALCULATE([Revenue], KEEPFILTERS(FILTER(ALL('Product'[Color]), 'Product'[Color] = "Red")))

As Marcus explained already, ALL() removes any filter context on 'Product'[Color] but KEEPFILTERS() allows to merge the filter specified in FILTER() as well as any other filter specified by the filter context of the same column.

Your question, however, was what the difference between your Red1 and your Red2 queries are. As you wrote as well, the result is the same in your example. As stated in this article here, Red1 iterates the entire product table whereas Red2 is a boolean expression. The latter will usually exhibit faster performance than passing an iterator/table function like FILTER() into calculate.

Upvotes: 1

Marcus
Marcus

Reputation: 4005

When you write a measure on the form:

Measure :=
CALCULATE ( 
    [Revenue] ,
    'Product'[Color] = "Red"
)

The filter is translated at query time to:

Measure :=
CALCULATE ( 
    [Revenue] , 
    FILTER ( 
        ALL ( 'Product'[Color] ) , 
        'Product'[Color] = "Red"
    )
)

Note that any filter in the current filter context is removed by the ALL function (and not by CALCULATE in and of itself).

If the filter context of this important, you can invoke KEEPFILTERS to change the semantics, in order to retain the filter on the specified column. Which means that a measure on the form:

Measure :=
CALCULATE ( 
    [Revenue] ,
    KEEPFILTERS ( 'Product'[Color] = "Red" )
)

Is translated to:

Measure :=
CALCULATE ( 
    [Revenue] , 
    FILTER ( 
        VALUES ( 'Product'[Color] ) , 
        'Product'[Color] = "Red"
    )
)

Upvotes: 3

Related Questions