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-01-01 的 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])返回计算 (MAX ('DimDate'[日期]),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屋！