CALCULATE的筛选器参数的多种写法-技术教程-四时宝库

本文翻译自SQLBI

摘要

CALCULATE 筛选器参数一个迭代器. 找到合适的粒度对控制结果和性能至关重要。本文描述了创建复杂DAX筛选器的可用选项

注意:

本文是描述的是参数为表筛选器时的情景，不考虑那些Directive参数(例如ALL, USERELATIONSHIP, CROSSFILTER,…)，这些函数是不通过返回一列值修改筛选上下文。

CACULATE中筛选器的基本原理

当CALCULATE中的筛选器参数全部都是表表达式，例如返回值为一张表中的一列或者多列，甚至是一整张表时：

CALCULATE (
<expression>,
table<column> = <value>
)

实际上上述的表表达式会被转换为：

CALCULATE(
<expression>,
FILTER(
ALL( table[column]),
Table[column] = <value>
)
)

上述筛选器参数转换的过程，对于CALCULATE和CALCULATETABLE是相同的。下面我们将用通用语法描述这两个函数中的筛选器参数:

CALCULATE(
<scalar expression>,
<filter1>,
<filter2>,
…..,
<filterN>
)

CALCULATETABLE(
<table expression>,
<filter1>,
<filter2>,
…..,
<filterN>
)

筛选器参数可以是逻辑表达式，也可是表表达式。

Table[column] = <value>
Filter( … )
ALL ( … )
And so on

复杂的筛选器参数

因为只有在逻辑表达式中引用一列时，DAX才会自动将其转为FILTER函数表表达式，所以你可以这样写DAX：

[Sales Red or Blue] :=
CALCULATE(
[Sales Amount],
Product[Color] = “Red” || Product[Color] = “Blue”
)

上述语法在DAX内部转换成下面将要提到的语法，当然了，你可以显示的方式按照下面的语法编写DAX度量：
[Sales Red or Blue] :=
CALCULATE(
[Sales Amount],
Filter(
ALL( Product[color]),
Product[color] = “Red” || Product[color] = “Blue”
)
)

然而，你不能在一个筛选器参数中引用两个不同的列：

[Sales Red or Contoso – invalid] :=
CALCULATE (
[Sales Amount],
‘Product’[Color] = “Red” || ‘Product’[Brand] = “Contoso”
)

在这种情况下，你必须显示编写FILTER表表达式，而不是依靠自动转换。只有当引用一个列时自动转换才会发生。

表筛选器

你可以在包含两列的整张表上添加一个Filter去筛选不同的两列:

[Sales Red or Contoso – invalid] :=
CALCULATE (
[Sales Amount],
FILTER(
‘Product’,
‘Product’[Color] = “Red” || ‘Product’[Brand] = “Contoso”
)
)

在上述DAX中，筛选器参数继承了表Product上已经存在的筛选器，如果当前筛选器中不包含红色的产品或者品牌为Contoso的产品，那么你的结果中也不会包含此类产品。更重要的是，这样写DAX不会覆盖已经存在的筛选器。因此，假如你筛选了品牌：Proseware，你将只会看到Proseware中的红色产品，不会看到品牌Contoso的任何产品。

ALL Columns Filter

你通过生成一张只包含你需要的列的特殊表来编写筛选器。通过使用ALL函数就可以生成这样的表格，该表包含指定的列的唯一组合，而且是在基表中存在的。

[Sales Red or Contoso – invalid] :=
CALCULATE (
[Sales Amount],
FILTER(
ALL( ‘Product’[Color], ‘Product’[Brand]),
‘Product’[Color] = “Red” || ‘Product’[Brand] = “Contoso”
)
)

在这种情况下，筛选器的基数(行数)可能低于指定列的笛卡尔积。为了获得这样的列表，DAX 引擎必须执行表扫描。对于大型表中的低基数列来说，这成本可能是昂贵的。

此筛选器将会覆盖引用列上已经存在的任何筛选器。例如，在切片器上已经筛选了品牌:Proseware, 其销售金额将是产品颜色为红色的所有产品的销售金额，再加上品牌为Contoso的所有颜色产品的销售金额（品牌为Contoso 颜色为红色的产品的金额只加一次）

CROSSJOIN Column Filter

另外一个解决办法是对指定的两列的所有值使用Crossjoin函数。利用Crossjoin函数，可以得到这两列值的所有组合，而不管组合是否在基础表中存在。

[Sales Red or Contoso – invalid] :=

CALCULATE (

[Sales Amount],
FILTER(
CROSSJOIN(
ALL( ‘Product’[Color]),
ALL(‘Product’[Brand]),
),
‘Product’[Color] = “Red” || ‘Product’[Brand] = “Contoso”
)
)

在本例中，筛选器的基数与所引用的列产生的笛卡尔积的基数是相同的。在这种情况下，DAX引擎不会再扫描了，而只使用两列中可用的值。但是，可能对于高相关性的高基数列来说，带来的成本可能是昂贵的，因此表中现有的组合可能要远远低于所有可能的组合。

该筛选器的结果与使用ALL函数的筛选器的结果是相同的，你可能仅仅观察到了这两种方法性能的不同。哪种是最好的取决于表和筛选器涉及到的列的基数。你可以使用CROSSJOIN来组合不同表中的列，而ALL函数只能组合同一张表中的列。

SUMMARIZE Columns Filter

在使用ALL/CROSSJOIN时，如果你想要保留现有的筛选器，你可以使用KEEPFILTERS

包裹ALL/CROSSJOIN函数，或者使用SUMMARIZE函数。

KEEPFILTERS：

[Sales Red or Contoso – invalid] :=
CALCULATE (
[Sales Amount],
KEEPFILTERS(
FILTER(
ALL( ‘Product’[Color], ‘Product’[Brand]),
‘Product’[Color] = “Red” || ‘Product’[Brand] = “Contoso”
)
)
)

[Sales Red or Contoso – invalid] :=
CALCULATE (
[Sales Amount],
KEEPFILTERS(
FILTER(
CROSSJOIN(
ALL( ‘Product’[Color]),
ALL(‘Product’[Brand]),
),
‘Product’[Color] = “Red” || ‘Product’[Brand] = “Contoso”
)
)
)

SUMMARIZE函数返回两列或多列的存在于基表中的组合，当两张表之间具有多对一关系时，SUMMARIZE函数也可以用在不同的表中。

[Sales Red or Contoso – invalid] :=
CALCULATE (
[Sales Amount],
FILTER(
SUMMARIZE(
‘Product’,
‘Product’[Color],
‘Product’[Brand]),
‘Product’[Color] = “Red” || ‘Product’[Brand] = “Contoso”
)
)

在这种情况下，与ALL/CROSSJOIN相比，筛选器的基数降低了，但是DAX引擎需要扫描表，以获得SUMMARIZE指定的列的现有组合。对于大型的表的低基数列，这可能花费的时间会更多。对于复杂的过滤调节，它可能比表扫描更快，但就性能而言，必须考虑备选的KEEPFILTERS的语法是否更好。

结论

在CALCULATE语句中，可能会有多种方式编写复杂的筛选器。筛选器参数的结果是一张包含一列或者多列的表格。对筛选器来说，决定其性能的就是这张参数表的行数。在使用CALCULATE时，通常情况下，直接使用一张表作为过滤器参数是编写复杂筛选器的最简便的方法， Filter迭代器的粒度可能过大导致其筛选器自身也可能会有比较高的成本，同时还要考虑到在扩展表上的相关成本。

欢迎加入我们

好了，我是BISeven，欢迎交流

四时宝库

程序员的知识宝库

CALCULATE的筛选器参数的多种写法