DAX 第五篇：CALCULATE函数详解

CALCULATE()函数是DAX中最重要和最复杂的函数，用于计算处在筛选上下文中的表达式的值。
CALCULATE(<expression>,<filter1>,<filter2> … )

第一个参数是用于计值的表达式，在计值之前，CALCULATE将首先计算<filter>参数，并与现有的外部筛选上下文作用，创建新的内部筛选上下文，最终在新的内部筛选上下文中完成表达式的计算。

计值表达式的后面是数量可变的过滤器参数，共有三种类型：

  • 返回表值的表达式，可以是具有任意列数的表，CALCULATE函数只考虑这些列值的现有组合。
  • 返回布尔值的逻辑表达式，是表值表达式的特殊形式，等价于Filter( ALL(Table[Column]), Table[Column]=Value)。
  • 筛选上下文修改器函数

在CALCULATE函数的作用域中，CALCULATE函数使用<filter>参数与现有的外部筛选上下文作用，创建新的内部筛选上下文，并在内部筛选上下文中计算表达式。CALCULATE函数不会修改外部的筛选上下文，一旦CALCULATE函数执行结束，其创建的内部筛选上下文就会消失。

CALCULATE函数的复杂之处在于：CALCULATE函数会创建内部筛选上下文。CALCULATE函数不修改外部的筛选上下文，它通过把filter参数与外部筛选上下文合并来创建新的内部筛选上下文。一旦CALCULATE函数的计算结束，用于计值的内部筛选上下文将消失。CALCULATE和CALCULATETABLE是DAX中仅有的两个可以创建内部筛选上下文的函数。

CALCULATE函数创建新的内部筛选上下文的方式：

  • 第一种是：<filter>参数会自动覆盖列上的过滤器。CALCULATE函数根据外部筛选上下文创建内部筛选上下文，对于filter参数用到的每个列，CALCULATE函数删除该列上的任何外部过滤器，并把filter参数中的过滤条件应用到内部筛选上下文中。
  • 第二种是：使用筛选上下文修改器函数，用于修改CALCULATE函数的内部筛选上下文，例如：REMOVEFILTERS，KEEPFILTERS，ALL , ALLEXCEPT、USERELATIONSHIP、CROSSFILTER等。

CALCULATE函数参数的执行顺序十分重要，参数的执行顺序分为三个顺序：

  • 第一顺序：执行筛选上下文修改器，REMOVEFILTERS，ALL , ALLEXCEPT、USERELATIONSHIP、CROSSFILTER
  • 第二顺序：执行其他<filter>参数，例如，特殊的 KEEPFILTERS 和其他<filter>参数，这些<filter>是独立执行的，并且各个<filter>参数通过逻辑与（AND）操作符共同作用的。
  • 第三顺序：执行<expression>参数，此时，<expression>参数处于CALCULATE函数新建的内部筛选上下文中，受到内部筛选上下文的过滤。

一，过滤器参数的默认作用

如果CALCULATE函数存在<filter>参数，那么CALCULATE 函数创建新的内部筛选上下文以计算表达式。对于每个<filter>表达式，当<filter>表达式未包含在 KEEPFILTERS 函数中时，有两种可能的结果：

  • 如果列（或表）不在外部筛选上下文中，那么新过滤器将被添加到内部筛选上下文以计算表达式。
  • 如果列（或表）已经在外部筛选上下文中，那么外部的过滤器将被新的过滤器重写以计算表达式。

注意：除了REMOVEFILTERS，ALL , ALLEXCEPT、USERELATIONSHIP、CROSSFILTER之外，其他<filter>参数的顺序不重要，各个<filter>参数都是独立执行的，并且各个<filter>参数通过逻辑与（AND）操作符共同作用的。

举个例子，计算产品Color=Blue的销售总额：

Blue Revenue =
CALCULATE(
    SUM(Sales[Sales Amount]),
    'Product'[Color] = "Blue"
)

这个DAX表达式等价于以下的表达式，可以看出，CALCULATE函数首先移除现有'Product'[Color]列上的过滤器，并在该列上创建新的过滤器Color=Blue。

Blue Revenue =
CALCULATE(
    SUM( Sales[Sales Amount] ),
    FILTER( ALL( 'Product'[Color] ), 'Product'[Color] = "Blue" )
)

注意：<filter>参数的顺序不重要，各个<filter>参数都是独立执行的，并且各个<filter>参数通过逻辑与（AND）操作符共同作用的。

例如，以下DAX表达式，总是返回空值，因为逻辑表达式Product[Color]=Blue AND Product[Color]=Red 返回的结果只能FALSE。

Blue Revenue3 =
CALCULATE(
    SUM(Sales[Sales Amount]),
    'Product'[Color] = "Blue",
    'Product'[Color] = "Red"
)

二，过滤器参数（即<filter>参数）的筛选上下文

CALCULATE函数的过滤器参数处于外部筛选上下文（即CALCULATE函数的原始筛选上下文）中计算的，这一点非常重要。CALCULATE函数函数会修改上下文，但是，只会在<filter>参数之后被计算之后才会发生。

对于以下的DAX表达式，按照年份统计每天的总销售数量，这个公式执行的操作是：

第一步：CALCULATE函数继承外部的筛选上下文，先执行过滤器参数，而后执行expression参数。

第二步：过滤器参数按照位置的先后顺序来计算，ALL(Sales）表示CALCULATE函数移除事实表Sales上的所有过滤器，

第三步：Values(DimDate[FiscalYear]) 表示获取 DimDate[FiscalYear]在筛选上下文中的值，并把该值作为过滤器参数的过滤器上下文。

第四步：CALCULATE函数根据外部的筛选上下文和两个过滤器参数创建自己的内部筛选上下文，计算表达式 SUMX(Sales, Sales[Quantity] * Sales[Net Price]) 的值。

第五步：对表达式 SUMX(Sales, Sales[Quantity] * Sales[Net Price]) 执行计算，首先Sales表被CALCULATE函数创建的内部筛选上下文过滤，然后SUMX迭代函数对过滤后的Sales表逐行执行计算，把所有行计算的结果加和，最后返回一个聚合值。

Total Sales by Year = CALCULATE( 
    SUMX(Sales, Sales[Quantity] * Sales[Net Price])
    ,ALL(Sales)
    ,Values(DimDate[FiscalYear])
)

三，过滤器调节函数

筛选上下文修改器函数，用于修改CALCULATE函数的内部筛选上下文，常用的过滤器调节函数有：REMOVEFILTERS，KEEPFILTERS，ALL , ALLEXCEPT、USERELATIONSHIP、CROSSFILTER等，其中KEEPFILTERS 函数是一个过滤器参数调节器（filter modifier）。CALCULATE函数的<filter>参数的执行顺序是非常重要的：

1，KEEPFILTERS 函数是一个过滤器参数调节器（filter modifier）

在CALCULATE函数中修改筛选上下文，默认的行为是替换相同列上的所有现有的过滤器，使用参数指定的过滤器来计算表达式，也就是说，在移除相同列上原有的过滤器之后，而采用新的过滤器上下文。

KEEPFILTERS 函数可以修改在CALCULATE函数的这种默认行为，不移除当前筛选上下文中原有的过滤器，把当前筛选上下文中的任何现有过滤器与参数中指定的过滤器进行比较，并把这两种过滤器的交集用作评估表达式的上下文，也就是说，向现有的过滤器上下文中增加新的过滤器。换句话说，CALCULATE 过滤器替换相同列上的筛选上下文，而 KEEPFILTERS 将过滤器添加到当前的筛选上下文，与当前筛选上下文是AND的关系。

KEEPFILTERS(<expression>)

举个例子，利用FILTER函数，可以缩小数据的范围：

Red Revenue =
CALCULATE(
    SUM( Sales[Sales Amount] ),
    FILTER('Product', 'Product'[Color] = "Red" )
)

这个DAX表达式的结果等价于以下的表达式，只不过FILTER函数不修改筛选上下文，只是缩小数据表的范围，而KEEPFILTERS函数保持原有的筛选上下文，并把新的过滤器添加到筛选上下文中。

Red Revenue =
CALCULATE(
    SUM( Sales[Sales Amount] ),
    KEEPFILTERS('Product'[Color] = "Red" )
)

注意：KEEPFILTERS 函数是一个过滤器参数调节器（filter modifier），执行顺序是先执行REMOVEFILTERS，KEEPFILTERS，ALL , ALLEXCEPT、USERELATIONSHIP、CROSSFILTER，后执行KEEPFILTERS函数。

举个例子，下面两个DAX表达式的结果是相同的，对于Bule Revenue2，先执行REMOVEFILTERS函数，移除Product[Color]上的过滤器，并新创建一个过滤器Product[Color]=Blue。

Blue Revenue =
CALCULATE(
    SUM(Sales[Sales Amount]),
    'Product'[Color] = "Blue"
)

Blue Revenue2 =
CALCULATE(
    SUM(Sales[Sales Amount]),
    KEEPFILTERS( 'Product'[Color] = "Blue"),
    REMOVEFILTERS('Product'[Color])
)

2，REMOVEFILTERS函数和ALL函数用于移除过滤器

移除指定表或列上的过滤器，不返回任何值。

REMOVEFILTERS([<table> | <column>[, <column>[, <column>[,…]]]])
ALL( [<table> | <column>[, <column>[, <column>[,…]]]] )

当ALL作为CALCULATE函数的<filter>参数时，ALL作为CALCULATE函数调节器，用于移除表上的所有过滤器，并且不会返回任何值。如果需要移除指定表或列上的过滤器，推荐使用REMOVEFILTERS。

无参数的ALL从数据模型中的所有表中清除筛选上下文，得到一个没有活动筛选器的筛选上下文。

3，修改关系

DAX在引用关系时，可以在CALCULATE函数的筛选上下文中临时修改关系的设置，比如引用处于不活跃状态的关系，修改现有关系的交叉过滤的方向设置。重写的关系设置只在查询时有效，不会影响数据模型中的关系设置。由于USERELATIONSHIP和CROSSFILTER处于第一执行顺序，因此，一旦在CALCULATE函数中修改关系，那么CALCULATE函数就会基于关系来修改外部的筛选上下文，其他<filter>参数就会基于这个筛选上下文来过滤。

结论：当在CALCULATE函数中应用USERELATIONSHIP时，所有的<filter>参数都使用同一个关系进行计算，不考虑彼此之间的先后顺序。可以认为USERELATIONSHIP是在CALCULATE函数执行之前修改了关系。

3.1，引用已有的关系

USERELATIONSHIP使用数据模型中已有的关系，通过关系两端的字段来指定关系：

USERELATIONSHIP(<columnName1>,<columnName2>)

在USERELATIONSHIP函数中，关系的状态是不重要的，但是关系必须事先创建于数据模型中，由于活跃的关系在DAX中是可以直接引用的，因此，该函数实际上用于引用处于不活跃（Inactive）状态的关系。

=CALCULATE(SUM(InternetSales[SalesAmount]), USERELATIONSHIP(InternetSales[ShippingDate], DateTime[Date]))

3.2，指定交叉过滤或者禁用关系

CROSSFILTER 使用数据模型中已有的关系，通过关系两端的字段来引用关系，通过<direction>参数来禁用关系，或使用关系。

在CROSSFILTER 中，关系的交叉过滤（cross-filtering）的设置是不重要的，也就是说，不论关系在数据模型中设置为single 方向或both方向，都不会影响函数的使用，CROSSFILTER将覆盖任何现有的交叉过滤（cross-filtering）设置。

CROSSFILTER(<columnName1>, <columnName2>, <direction>)

参数direction 有4个可用值：

  • None ：禁用关系
  • Both ：设置交叉过滤的方向为双向
  • OneWay_LeftFiltersRight：设置交叉过滤的方向为单向，<columnName1>过滤<columnName2>，不能用于1对1的关系中和多对多的关系中，
  • OneWay_RightFiltersLeft：设置交叉过滤的方向为单向，<columnName2>过滤<columnName1>，不能用于1对1的关系中和多对多的关系中，

CROSSFILTER 函数的注意事项：

  • CROSSFILTER函数只能用于数据模型中已经存在的关系。
  • 在Calculate等函数的上下文，CROSSFILTER函数会覆盖在数据模型中设置的关系的cross-filter的方向。
  • 如果CALCULATE 嵌套，并且多个 CALCULATE 表达式包含 CROSSFILTER 函数，那么最内层的CROSSFILTER 是在发生冲突或歧义时优先级最高。

四，嵌套 CALCULATE 函数

过滤器参数都是在在CALCULATE 函数的外部筛选上下文中执行的，并且每个过滤器参数都是独立计算的。在同一个CALCULATE 函数中，过滤器参数的顺序并不十分重要，每个过滤器参数都是独立计算的。

对于嵌套CALCULATE 函数，可以认为，外层的CALCULATE 函数的筛选上下文是内层CALCULATE 函数的外部筛选上下文，如果嵌套的CALCULATE 函数的过滤器参数有相同的列，那么外层CALCULATE 函数的相同列上的过滤器会被覆盖，不会被计算到。

举个例子，对于内部的CALCULATE 函数，外部筛选上下文有一个过滤器 Product[Color]=Bule，而自身的过滤器Product[Color]=Red会覆盖外部的筛选上下文，总是返回红色产品的销售额。

Red Revenue =
CALCULATE(
     CALCULATE(
                SUM(Sales[Sales Amount])
                ,'Product'[Color] = "Red"
     )
     ,'Product'[Color] = "Blue"
)

如果内层的CALCULATE 函数需要保留外层嵌套的CALCULATE 函数，那么需要在内层的CALCULATE 函数可以在过滤器参数上使用过滤修改器函数KEEPFILTERS，下面的两个DAX表达式是等价的。

Red Revenue =
CALCULATE(
     CALCULATE(
                SUM(Sales[Sales Amount])
                ,KEEPFILTER('Product'[Color] = "Red")
     )
     ,'Product'[Color] = "Blue"
)

Red Revenue =
CALCULATE(
      SUM(Sales[Sales Amount])        
      ,'Product'[Color] = "Red"
      ,'Product'[Color] = "Blue"
)

 

 

参考文档：

DAX Context

详解CALCULATE系列-基础篇

CALCULATE 庖丁解牛系列-- 扩展表（1）

posted @ 2019-07-23 13:20  悦光阴  阅读(15821)  评论(0)  编辑  收藏  举报