如果数据模型是 Power BI 的灵魂,那么 CALCULATE 可以说是 DAX 函数的核心
介绍 CALCULATE
CALCULATE 和 CALCULATETABLE 是 DAX 中惟二可以修改筛选上下文的函数。实际上,CALCULATE 创建了一个新的筛选上下文,然后在新的上下文中计值表达式。因为新上下文的源自现有上下文,所以我们可以说它修改了计值上下文。
——— 本文内容较多,请结合右侧居中的文章导航进行导读 ————
语法结构
现在我们开始研究 CALCULATE 的语法:
- [Measure] := CALCULATE( Expression,Condition1, ...ConditionN )
复制代码
CALCULATE 接受任意数量的参数,其中只有第一个是必填参数,即需要计值的表达式。我们将第一参数之后的条件表达式称为筛选器参数。
一个典型的 CALCULATE 公式写法是这样的:
示例公式
- A. 度量值名称 Store Sales。
- B. 等号运算符 ( = ) 表示公式开始。
- C.CALCULATE 函数,根据自身使用的筛选器参数计值表达式。
- D. 括号 () 括住包含一个或多个参数的表达式。
- E. 作为 CALCULATE 第一参数的 [Total Sales] 度量值。 定义Total Sales 度量值的公式为 SUM(Sales[SalesAmount])。
- F. 逗号 ( , ) 分隔第一参数和其后的筛选器参数。
- G. 使用完全限定名的 Channel[ChannelName]列,此列中每行的值代表一个渠道:比如 Store、Online 等。
- H. 将 Store 的值作为筛选器。 G 和 H 组成了 CALCULATE 内部一个完整的筛选器。
此公式使用 Channel[ChannelName]=“Store”作为筛选器,筛选 Channel[ChannelName] 列中值为 Store 的行,在筛选后的结果中计算 Total Sales 度量值所定义的销售额。
CALCULATE 与上下文转换
CALCULATE 有一个非常重要的任务:将任何现有的行上下文转换为等效的筛选上下文。在理解上下文转换一文中已经对此进行了详细的讨论。我们在这里提到它的原因是,最好记住这个非常重要的事实,以避免重读这部分内容:CALCULATE 根据现有的行上下文创建等效的筛选上下文。
CALCULATE 与筛选器参数
CALCULATE 接受三种类型的筛选器:
表形式的值列表
在这种情况下,你将提供需要在新的筛选上下文中显示的值列表,筛选器可以是单列形式的表,也可以是具有多个列的表,就像将整张表作为筛选器一样,FILTER 是其中最常用的函数之一。
布尔条件
例如 Product[Color] = “白色”。这些筛选器需要在单个列上生效,因为结果必须是单个列的值列表。
对于布尔条件的筛选器,DAX 将把它内部转换值列表的形式。所以每当你编写:
- [Sales Amount Red Products] :=
- CALCULATE (
- SUM ( Sales[SalesAmount] ),
- Product[Color] = "Red"
- )
复制代码
DAX 将表达式转换为下面这个:
- [Sales Amount Red Products] :=
- CALCULATE (
- SUM ( Sales[SalesAmount] ),
- FILTER ( ALL ( Product[Color] ), Product[Color] = "Red" )
- )
复制代码
因此,当你在单个列上使用布尔条件作为筛选器参数时, DAX 检测到此列并将其置于 FILTER 表达式中迭代,这一步在后台自动生成。如果布尔表达式引用了更多的列,那么你必须以显式的方式编写 FILTER 函数,稍后在筛选复杂条件一节将介绍这种情况。
表形式的值列表和布尔条件的本质都是表,理解布尔条件的这种等价转换对解读复杂公式非常有帮助,你最好将这种等价转换在大脑中形成条件反射
CALCULATE Modifiers
除了以上两种表形式的筛选器外,CALCULATE 还接受 ALL、ALLSELECTED、KEEPFILTERS、USERELATIONSHIP 等函数作为筛选器参数,它们不像前两种筛选器那样直接引入新的筛选上下文,而是改变新筛选上下文生成的方式,比如 KEEPFILTERS 改变当前筛选器与原始筛选上下文合并的方式。CALCULATE 调节器是一类常用且重要的函数,将在独立章节中介绍。
前文回顾
现在,我们可以重温度量值:
- [SalesPctWithCalculate] :=
- DIVIDE (
- SUM ( Sales[SalesAmount] ),
- CALCULATE (
- SUM ( Sales[SalesAmount] ),
- ALL ( 'Product Category' ),
- ALL ( 'Product Subcategory' )
- )
- )
复制代码
让我们把注意力集中到分母的 CALCULATE 表达式,它总是计算同样的内容:销售额的总和。然而,我们已经知道公式本身可以返回很多不同的值,这取决于 DAX 的计算环境。这一次对销售额总和的计算位于 CALCULATE 表达式内部,表达式的上下文不再是初始的上下文。我们只需要理解真实的上下文是怎样的,它们来自 CALCULATE 引入的附加参数。
- 第一个筛选器参数为 ALL(‘Product Category’)。ALL 移除产品类别表上已有的任何筛选器,本例中来自透视表行标签。
- 第二个筛选器参数也是同理,ALL(‘Product Subcategory’)移除了产品子类别表任意列上的筛选器。
值得注意的重要信息是,CALCULATE 并没有替换来自颜色列的筛选器,它只从产品类别和产品子类别表的列中移除筛选器, 因此,最终的筛选上下文是用户所选的颜色(包含所有的类别和子类别)。
在数据透视表中使用这个新公式可以显示正确的值,如图所示。
使用 CALCULATE 计算的百分比展示了正确的结果
细心的读者可能会停下来问:“但是,这种解释没有意义。你从产品类别和产品子类别中删除了筛选器,但公式正在从销售表中求和。那么是谁删除了销售表的筛选器呢?”这是一个非常好的问题,事实上,我们的描述还缺失了一些非常重要的东西:CALCULATE 计算出新的筛选上下文后, 会在表达式计值之前将其应用于数据模型中。
当 DAX 将筛选上下文应用到一个表中时,我们已经从上一章了解到筛选器通过其定义的关系(单向或双向筛选)传递。事实证明,我们从产品类别和产品子类别中删除了筛选器,当 DAX 应用新的筛选上下文时,它会将其传递到事实表,事实表位于从产品类别到销售表的关系链的多端。通过从产品类别和产品子类别中删除筛选器, 我们也在销售表中删除了相应的筛选器。
CALCULATE 计值规则
CALCULATE 计值规则是理解公式运行逻辑的重要知识,这里给出的是易于理解的简要描述,在本章最后一文:CALCULATE 指南,你将看到完备的描述。
- 感知当前外部筛选上下文*并复制一份进入新的筛选上下文。
- 为每个筛选器参数计值,对于参数中的每个条件,生成对应列的有效值列表。
- 如果有两个或更多筛选器参数影响同一列时,使用 AND 运算符将它们合并(或者用数学术语描述:使用交集)。
- 使用新的筛选条件替换当前列已有的筛选器。即,对于那些已经存在筛选器的列,新的筛选器将替换掉现有筛选器。如果当前列不存在任何筛选器,DAX 直接施加新的筛选器。
- 一旦得到了新的筛选上下文,CALCULATE 就在此上下文中计值第一参数(表达式),计值完成后恢复初始筛选上下文,返回计算结果。
筛选器的 and 运算
*注:外部筛选上下文是在调用公式之前已经存在的筛选上下文,比如透视表的行列标签、切片器、报表中的交叉筛选、筛选器都可以提供外部筛选上下文。这类上下文在微软文档中也被叫做查询上下文 Query Context,这是一种更细分的名称,现在仍有这种叫法
CALCULATE 应用实例
现在你已经了解了 CALCULATE 的基本知识,或者说至少了解了它为何如此有用,本章接下来的部分将专门介绍它的各种用法示例。它们对于深入学习和理解此函数非常重要。实际上,CALCULATE 本身是一个非常简单的函数。复杂性(我们赋予它的重要性)来自这样一个事实:使用 CALCULATE,思考必须依据筛选上下文,并且最终可能在一个公式中使用多套上下文,这使得计值过程难以追踪。根据之前的学习经验,通过实例学习是理解 CALCULATE 和筛选上下文的最好方法。
筛选单列
使用 CALCULATE 最简单的方式是筛选单列。例如,假设你希望创建一个度量值,该度量值总是返回黑色产品的销售额,而不考虑对颜色所做的选择。公式很容易编写:
- [SalesAmountBlack] :=
- CALCULATE (
- SUM ( Sales[SalesAmount] ),
- Product[Color] = "Black"
- )
复制代码
将此公式应用到透视表中,得到的结果如下图所示:
SalesAmountBlack 总是返回黑色产品的销售额,忽略当前筛选上下文
可以看出无论当前行标签的筛选上下文是何种颜色,SalesAmountBlack 总是返回黑色产品的销售额。
如果你把注意力放到透视表的第三行(蓝色产品),公式是这样计值的:
公式在筛选上下文中开始计算,其中颜色的惟一值是蓝色。然后,CALCULATE 引入了一个新的筛选器参数(颜色=黑色),将其应用到新的筛选上下文,它将替换现有的条件,删除蓝色筛选器,并用黑色筛选器替换它。此行为发生在所有的行(也就是所有的颜色),这就是为什么所有的行都有相同结果的原因
显然,因为 CALCULATE 覆盖的唯一一列是对颜色的选择,所以其他列仍然保留了各自的筛选器。例如,如果将日历年放在列上,你会看到所有颜色的结果都是相同的,但是不同的年份会有所不同,如图所示。
SalesAmountBlack 只覆盖对颜色的选择,对于来自其他列的筛选(年份)仍然有效
在筛选器参数一节中我们已经知道,当使用布尔条件时,它可以改写为 FILTER+ALL 的表形式的筛选器,比如上面的公式等价于:
- [SalesAmountBlack] :=
- CALCULATE (
- SUM ( Sales[SalesAmount] ),
- FILTER ( ALL ( Product[Color] ), Product[Color] = "Black" )
- )
复制代码- [SalesAmountBlack] :=
- CALCULATE (
- SUM ( Sales[SalesAmount] ),
- FILTER ( VALUES ( Product[Color] ), Product[Color] = "Black" )
- )
复制代码
你可能好奇这种等价形式的 ALL 函数是否可以省略,因为 FILTER 第一参数必须是表,所以你不能直接省略 ALL,而是要找到一个类似的表函数替换 ALL。VALUES 是个不错的选择,你可以参考 VALUES 形式的公式写法,它在语法上没有任何问题,但是无法得到和原公式相同的结果。原因在于 VALUES 接受来自外部上下文的筛选,而 ALL 不会,所以当你将它放入透视表后,只有颜色为 Black 的行会返回结果,其他行返回空值。
筛选单列不能引用多条件
筛选单个列很简单。而一个不那么明显的事实是,如果希望使用多个条件作为 CALCULATE 的筛选器,一次也只能筛选一列。例如,你可能希望创建一个度量值,只计算单价至少是单位成本两倍的产品的销售额。你可以试试这个公式:
- [HighProfitabilitySales] :=
- CALCULATE (
- SUM ( Sales[SalesAmount] ),
- Product[Unit Price] >= Product[Unit Cost] * 2
- )
复制代码
可以看到,这一次,筛选器包含两列:单位成本和单位价格。即使看起来 DAX 可以很容易地为每个产品计值该表达式,这种语法形式也是不被允许的。结合上文介绍过布尔筛选器的等价形式,错误原因是 CALCULATE 无法确定该筛选器是否应该替换现有的单价筛选器、单位成本筛选器还是都不替换。事实上,如果尝试上面这种写法,你会得到这样一个错误提示:
语义错误:该表达式包含多列,但只有一个列可用在用作表筛选表达式的 True/False 表达式中
你无法使用布尔表达式来编写这样的公式。如果需要在 CALCULATE 条件参数中同时使用多个列,则需要使用不同的语法,它提供一个值列表而不是条件判断。
- [HighProfitabilitySales] :=
- CALCULATE (
- SUM ( Sales[SalesAmount] ),
- FILTER (
- Product,
- Product[Unit Price] >= Product[Unit Cost] * 2
- )
- )
复制代码
这次我们没有使用布尔表达式,而是使用返回表的语法作为 CALCULATE 的筛选器参数。此外,我们没有只筛选一列;我们筛选了整个产品表。在图 5-9 中,你可以看到实际的 HighProfitabilitySales 结果。
图 5-9 HighProfitabilitySales 显示了售价高于成本两倍的产品销售额
在本例中,CALCULATE 计值条件表达式:FILTER 的结果是一个包含多个列的表(它包含产品表的所有列)。当新的条件计值结果进入筛选上下文时,实际上产品表的所有现有条件都被这个新筛选器所替代。换句话说,使用这个真实表作为 FILTER 函数的第一个参数,我们可以有效地替换该表所有列上的所有条件。
在阅读过前面的解释之后,你应该注意到有些东西并没有讲清楚。我们说,CALCULATE 中的 FILTER 表达式替换了产品表之前存在的所有筛选器,因为 FILTER 返回的表包含产品表的所有列。然而,公式返回的值在每一行都是不同的。
例如,在蓝色产品这一行,HighProfitabilitySales 返回了高利润率蓝色产品的销售额。而根据我们目前所学,公式应该返回所有高利润产品销售额才对。要么是因为颜色筛选器尚未被替换、要么是发生了更复杂的情况。因为我们知道颜色筛选器已被替换,所以需要进一步调查以更好地理解计值流。下面是公式使用的代码:我们对行进行编号,以便更容易地引用公式的各个部分。
- CALCULATE (
- SUM ( Sales[SalesAmount] ),
- FILTER (
- Product,
- Product[Unit Price] >= Product[Unit Cost] * 2
- )
- )
复制代码
CALCULATE 是公式使用的第一个函数,我们都知道,CALCULATE 计值的第一步是计算它的筛选器参数。所以在本例中,它从第三行 FILTER 表达式处开始计算。
FILTER 是一个迭代器,它迭代产品表(见第 4 行)。由于筛选上下文的存在,产品表并非全部可见,只有在当前筛选上下文环境中的行才是可见的。现在的问题是“DAX 在第 4 行对产品表的计算是基于哪个筛选上下文环境?” 请记住 CALCULATE 目前还没有创建新的计值上下文,稍后,在对所有筛选器参数完成计算之后它才会这么做。因此可以推断出,CALCULATE 的筛选器是在初始筛选上下文中计算的,而非其自身创建的筛选上下文。虽然这看似显而易见,但在这方面欠缺考虑是 DAX 公式错误的主要来源之一。
第 4 行中的产品表只显示初始筛选上下文中的可见部分。对于图 5-9 中的蓝色行,该上下文仅显示蓝色产品。因此,FILTER 将迭代蓝色产品,只返回其中的高盈利部分。然后 CALCULATE 将移除颜色列现有的筛选器,但该筛选器已经影响了 FILTER 的结果,于是导致了你所观察到的行为。正确地理解筛选器的计值流非常重要,颜色列的筛选器在 CALCULATE 计值的参数表达式内部被替换,而不是在 CALCULATE 创建的筛选上下文内部被替换。换句话说,CALCULATE 的筛选器参数是在之前的筛选上下文中计算的。在这之后 CALCULATE 才能创建它自己的筛选上下文,在这个上下文中计算表达式(第一参数)。
为了充分理解原理,我们对公式稍作修改:
- [HighProfitabilityALLSales] :=
- CALCULATE (
- SUM ( Sales[SalesAmount] ),
- FILTER (
- ALL ( Product ),
- Product[Unit Price] >= Product[Unit Cost] * 2
- )
- )
复制代码
这次我们使用 FILTER(ALL(Product)),而非 FILTER(Product)。FILTER 将不只对蓝色产品迭代,它将总是迭代所有产品,因为 CALCULATE 替换了颜色筛选器。由此产生的行为如图所示
HighProfitabilyALLSales 显示颜色筛选器在 CALCULATE 内部被有效地替换
HighProfitabilityALLSales 总是显示所有高利润率产品的销售额,有效地忽略原有颜色筛选器。
让我们从第一个例子中总结一些结论:
- CALCULATE 内部可以使用 TRUE/FALSE 条件,但是使用这些条件仅能引用该表达式中的一列,否则会出现语法错误。
- FILTER 或任何其他表函数都可以作为 CALCULATE 中的一个筛选器参数。在这种情况下该表中的所有列都是新的筛选上下文中的一部分。这意味着该表参数将替换在相同列上的任何现有筛选器。
- 如果使用 FILTER,那么 FILTER 在初始筛选上下文中计值。另一方面,如果使用布尔条件,那么 CALCULATE 仅替换受影响列中的现有筛选上下文。
筛选复杂条件
当使用多个筛选器时,CALCULATE 在新创建的筛选上下文中对所有筛选条件执行逻辑 AND 运算。所以,如果你想筛选所有由 Tailspin Toys 制造的黑色产品,你可以这样定义表达式:
- [Calculate Version] :=
- CALCULATE (
- SUM ( Sales[SalesAmount] ),
- Product[Brand] = "Tailspin Toys",
- Product[Color] = "Black"
- )
复制代码
因为 CALCULATE 对这两个条件取交集(AND),你可能认为下面这个表达式和它是等价的:
- [FILTER Version] :=
- CALCULATE (
- SUM ( Sales[SalesAmount] ),
- FILTER (
- Product,
- AND (
- Product[Brand] = "Tailspin Toys",
- Product[Color] = "Black"
- )
- )
- )
复制代码
实际上,这两种表达式是不同的,之前已经解释过原因,相信你已经了解了这一点。但是由于这个主题的复杂性和本节后续讨论概念的重要性,因此值得再次重复:在使用布尔表达式的公式中,品牌和颜色列忽略已有的筛选上下文;而在使用 FILTER 的公式中,公式被应用之前的筛选上下文都考虑了这两列。
因此,Calculate Version 总是返回黑色 Tailspin Toys 的销售额,而 FILTER Version 只返回那些内部条件组合已存在于外部筛选上下文(产品名称、颜色和品牌)中的销售额;否则返回空值。你可以在图中观察到这种行为。
两个公式的计算结果不同,尽管它们看起来非常相似
区别在于 FILTER 迭代的是由外部筛选上下文过滤的表。记住下面的公式:
- [Sales of Tailspin Toys] :=
- CALCULATE (
- SUM ( Sales[SalesAmount] ),
- Product[Brand] = "Tailspin Toys"
- )
- ------- 它等价于下面的公式 ---------
- [Sales of Tailspin Toys] :=
- CALCULATE (
- SUM ( Sales[SalesAmount] ),
- FILTER (
- ALL ( Product[Brand] ),
- Product[Brand] = "Tailspin Toys"
- )
- )
复制代码
在第二个公式中,通过使用 ALL ( Product[Brand] ),我们明确忽略了当前筛选上下文中的品牌列。理解公式的这种行为非常重要,再怎么强调它的重要性也不为过。即使这里使用的表达式是出于演示目的,你在自己编写表达式时也会遇到类似的场景,最终很可能得到类似的奇怪结果。为了理解公式的行为,你必须理解其中的上下文行为。
作用于单个列时,上面使用的等效公式可以很好的执行。当我们的示例有两列时,你可能想通过尝试下面的方案将等效公式扩展到多列场景:
- FilterAll Version :=
- CALCULATE (
- SUM ( Sales[SalesAmount] ),
- FILTER (
- ALL ( Product ),
- AND (
- Product[Brand] = "Tailspin Toys",
- Product[Color] = "Black"
- )
- )
- )
复制代码
此公式不满足你在本节中看到的第一个公式所解决的需求。如果使用 ALL (Product),那么通过忽略整个表的筛选上下文,你也忽略了这两列的筛选上下文。但是,忽略整个表上的筛选上下文仍然会得到不同的行为。为了看到效果,我们需要使透视表更加复杂。在下图中,我们在行上添加了类别名称。
对产品表使用 FILTER 和 ALL 函数仍然不能得到相同的行为
如你所见,FilterAll 版本忽略了整个产品表上的筛选上下文,甚至显示了计算机类别的值,而 Calculate 版本在此处显示空白值。原因是 Calculate 版本仅忽略颜色和模型名称的筛选上下文,而 FilterAll 版本会忽略整个表上的筛选上下文,从而也忽略了类别。
为了找到正确的公式,你必须考虑列而不是表。我们既不能对产品表使用 FILTER(因为它包含完整的初始筛选上下文),也不能使用 ALL ( Product ) (因为它忽略了所有筛选器)。相反,我们需要生成这样一个产品表,它移除和制造商信息有关的筛选器,保留任何其他已有的筛选器。我们已经知道有一个函数允许我们精细化的处理筛选上下文:CALCULATE。唯一的问题是,CALCULATE 只能返回标量值,而这一次我们希望返回整个表,因为我们需要它作为 FILTER 函数的参数。幸运的是,CALCULATE 还有一个兄弟函数,它不返回单个值,而是返回一个表,它是 CALCULATETABLE,下一篇文章中介绍会详细介绍。
多条件并列时的计值策略
在 FILTER 函数一文的CROSSJOIN 也得到两列组合的不重复值,这两种方法的性能有所区别,最佳选择取决于表的基数和筛选器中涉及的列的基数。但是,你可以使用 CROSSJOIN 来组合不同表的列,这是 ALL 无法实现的。
用 SUMMARIZE 返回表
- [Sales Red or Contoso - SUMMARIZE filter] :=
- CALCULATE (
- [Sales Amount],
- FILTER (
- SUMMARIZE (
- 'Product',
- 'Product'[Color],
- 'Product'[Brand]
- ),
- 'Product'[Color] = "Red" || 'Product'[Brand] = "Contoso"
- )
- )
复制代码
如果你需要像 FILTER 函数那样保留现有的筛选器,可以使用 SUMMARIZE,SUMMARIZE 函数生成两个列之间的现有组合列表,当表之间通过一对多关系连接到一起时,SUMMARIZE 可以组合来自不同表的列。
使用 ALL+KEEPFILTERS 或者 CROSSJOIN+KEEPFILTERS,也可以得到与 SUMMARIZE 方案相同的结果
- [Sales Red or Contoso - ALL KEEPFILTERS] :=
- CALCULATE (
- [Sales Amount],
- KEEPFILTERS (
- FILTER (
- ALL ( 'Product'[Color], 'Product'[Brand] ),
- 'Product'[Color] = "Red" || 'Product'[Brand] = "Contoso"
- )
- )
- )
复制代码- [Sales Red or Contoso - CROSSJOIN KEEPFILTERS] :=
- CALCULATE (
- [Sales Amount],
- KEEPFILTERS (
- FILTER (
- CROSSJOIN (
- ALL ( 'Product'[Color] ),
- ALL ( 'Product'[Brand] )
- ),
- 'Product'[Color] = "Red" || 'Product'[Brand] = "Contoso"
- )
- )
- )
复制代码
在这个例子中,与 ALL/CROSSJOIN 方案相比,筛选 SUMMARIZE 的数据量最少,但是为了获得 SUMMARIZE 中指定的列的现有组合,你需要承担扫描表的开销。对于大表中基数较低的列,这样做的代价可能很高。对于复杂的筛选条件,KEEPFILTERS 可能比扫描表更快。最终在性能方面的取舍取决于数据分布。
本节没有原配的案例,为了方便大家练习,你可以从下方下载权威指南的示例文件自己练习
案例文件下载
|