Difference between CALCULATE(m, x=red) vs CALCULATE(m, KEEPFILTERS(x=red))

Question

What is the difference between CALCULATE(m, x=red) and CALCULATE(m, KEEPFILTERS(x=red))

Apparently they are not the same. I found docs and explanation but I still do not get it.

https://docs.microsoft.com/en-us/dax/keepfilters-function-dax

https://dax.guide/keepfilters/

Answer 1

Your first measure, without KEEPFILTERS overrides all other filters applied to field x.

The second measure, using KEEPFILTERS maintains the filter context on field x, and applies the new filter context as a subset of existing filters (or blank, if no overlap of filter contexts).

Here's a simple example PBIX file to demonstrate - play with the colour slicer, and see the difference in the two measures: https://pwrbi.com/so_57850298/

This SQLBI.com article explains it well.

Answer 2

It is helpful to understand a bit more about how simple predicates in CALCULATE are evaluated. The following two expressions are equivalent; in fact, the first is just syntactic sugar for the second - the former is rewritten to the latter behind the scenes:

CALCULATE ( [m], 'T'[Col] = "Red" )

and

CALCULATE (
  [m],
  FILTER (
    ALL ( 'T'[Col] ),
    'T'[Col] = "Red"
  )
)

FILTER is an iterator that takes a table as its first argument and a predicate to be evaluated in row context as its second argument. It removes any rows from the input table where the predicate is false.

Thus, CALCULATE manipulation of filter context is actually almost entirely manipulations of tables. If you're comfortable with the relational algebra, the tables in args2-N of CALCULATE are tables which are left semijoined to the table(s) being operated on in the expression in CALCULATE's arg1. These semijoins depend on relationships being defined in the data model.

So the pattern of FILTER ( ALL ( 'T'[Col] ), <predicate> ) ignores any external filter context on 'T'[Col] and replaces that with a new filter context that you are defining.

Now for KEEPFILTERS. I am not 100% positive that this is just syntactic sugar, but I believe it is. Either way, the two expressions below are semantically equivalent - they will always return the same values:

CALCULATE ( [M], KEEPFILTERS ( 'T'[Col] = "red" ) )

and

CALCULATE (
  [M],
  FILTER (
    VALUES ( 'T'[Col] ), // this is the only difference from the first expansion
    'T'[Col] = "red"
  )
)

You can see that the KEEPFILTERS expansion is using VALUES instead of ALL. So, ALL returns all unique values from the named column, ignoring any filter context on that column (it also has other forms where it can operate on more than one column, but that is not relevant to this discussion). VALUES returns the unique values from the named column in the current filter context.

Another way to think of this is as follows. Assume that the value "red" does exist in 'T'[Col]. FILTER ( ALL ( 'T'[Col] ), 'T'[Col] = "red" ) will always return the 1-column, 1-row table of 'T'[Col] with the value "red". FILTER ( VALUES ( 'T'[Col] ), 'T'[Col] = "red" ) will always return a 1-column table, either with 0 or 1 row; if the external filter context includes 'T'[Col]="red", then it will return the 1-row table with 'T'[Col]="red", whereas if the external filter context does not include that value, it will return the empty table.

Again, the table output of the FILTER expressions above is treated as the right side table in a left semi-join.

Note, especially, that all of the above is based on single columns. You might get thrown for a loop if there are multiple columns contributing filter context. Here is an easy-to-understand example. We define two measures and put them into a table visual with 'DimDate'[Year] and 'DimDate'[Date].

Prior Date = 
VAR CurrentDate = MAX ( 'DimDate'[Date] )
RETURN
  CALCULATE (
    MAX ( 'DimDate'[Date] ),
    'DimDate'[Date] = CurrentDate - 1
  )

Prior Day ALL DimDate = 
VAR CurrentDate = MAX ( 'DimDate'[Date] )
RETURN
  CALCULATE (
    MAX ( 'DimDate'[Date] ),
    ALL ( 'DimDate' ),
    'DimDate'[Date] = CurrentDate - 1
  )

And here's what they return in our table visual:

Arithmetic with dates is defined in DAX and <date> - 1 will always return the prior date. So CurrentDate - 1 on 2019-01-01 is 2018-12-31. But in our visual, we have filter context coming from both 'DimDate'[Year] and 'DimDate'[Date], so in the first measure, we're calculating MAX ( 'DimDate'[Date] ) in the filter context of 'DimDate'[Year]=2019 and 'DimDate'[Date]=2018-12-31 (the context manipulation in our CALCULATE). There are no rows in 'DimDate' that simultaneously match both of those conditions, so the first version returns blank. The second version clears all filter context coming from 'DimDate', so the only context remaining is what we explicitly apply with 'DimDate'[Date] = CurrentDate - 1.

Note that the example above would only return values for totals when we use KEEPFILTERS.

Prior Date KEEPFILTERS = 
VAR CurrentDate = MAX ( 'DimDate'[Date] )
RETURN
  CALCULATE (
    MAX ( 'DimDate'[Date] ),
    KEEPFILTERS ( 'DimDate'[Date] = CurrentDate - 1 )
  )

This only works on totals, because at a detail level, there is no way for KEEPFILTERS ( 'DimDate'[Date] = CurrentDate - 1 to return any values. It's saying, essentially "find me a date that is one less than itself", which is obviously impossible. But at a grand total level, there are many dates in context, and so we're filtering a table of many contiguous dates. So our measure can return something for totals.