CALCULATE（m，x = red）与CALCULATE（m，KEEPFILTERS（x = red））之间的差异 [英] Difference between CALCULATE(m, x=red) vs CALCULATE(m, KEEPFILTERS(x=red))

问题描述

CALCULATE（m，x = red）和 CALCULATE（m，KEEPFILTERS（x = red））

显然它们并不相同。我找到了文档和说明，但仍然不明白。

在DAX中定义了带有日期的算法，而< date> -1 将始终返回上一个日期。因此，2019年1月1日的 CurrentDate-1 是2018-12-31。但是在视觉效果中，我们有来自 DimDate [Year]和 DimDate [Date]的过滤器上下文，因此在第一个度量中，我们要计算 MAX（'DimDate'[Date] ）在'DimDate'[Year] = 2019和'DimDate'[Date] = 2018-12-31的过滤器上下文中（在我们的 CALCULATE ）。 DimDate中没有同时符合这两个条件的行，因此第一个版本返回空白。第二个版本清除了所有来自 DimDate的过滤器上下文，因此剩下的唯一上下文是我们显式应用的 DimDate [Date] = CurrentDate-1 。

请注意，上面的示例仅在使用 KEEPFILTERS 时返回总计值。

 优先日期KEEPFILTERS = 
 VAR CurrentDate = MAX（'DimDate'[Date]）
 RETURN 
计算（
 MAX（'DimDate'[Date]），
 KEEPFILTERS（'DimDate'[Date] = CurrentDate-1）
）
  

这仅适用于总计，因为在详细信息级别上， KEEPFILTERS（'DimDate'[Date] = CurrentDate-1 无法返回任何值。也就是说，本质上是找到一个日期小于其自身的日期，这显然是不可能的。但是，在总计水平上，上下文中有许多日期，因此我们要过滤一个包含多个连续日期的表。因此，我们的度量值可以返回总计值。

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/

解决方案

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.

这篇关于CALCULATE（m，x = red）与CALCULATE（m，KEEPFILTERS（x = red））之间的差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！