理解 CALCULATE

如果数据模型是 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 是其中最常用的函数之一。

布尔表达式

返回布尔值（True 或者 False）的表达式，是简单逻辑的表筛选器的一种简化写法。布尔表达式使用比较运算符同一个常量做比较，例如 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 函数。

注意，2021 年 9 月之后的 Desktop 支持在布尔表达式中使用聚合函数，例如

Total sales on the last selected date =
CALCULATE (
    SUM ( Sales[Sales Amount] ),
    'Sales'[OrderDateKey] = MAX ( 'Sales'[OrderDateKey] )   //这里没有直接使用常量，也是可以的
)

由于聚合函数忽略行上下文，所以这里的 MAX 返回的其实也是常量，符合规范，需要注意的是老版本的 EXCEL 里不支持这种写法。但如果把聚合函数换成度量值或者每行返回不同结果的函数，则违反了与布尔表达式与常量进行比较的原则。

CALCULATE Modifiers

除了以上两种表形式的筛选器外，CALCULATE 还接受 ALL、ALLSELECTED、KEEPFILTERS、USERELATIONSHIP 等函数作为筛选器参数，它们不像前两种筛选器那样直接引入新的筛选上下文，而是改变新筛选上下文生成的方式，比如 KEEPFILTERS 改变当前筛选器与原始筛选上下文合并的方式。CALCULATE 调节器是一类常用且重要的函数，将在独立章节中介绍。

前文回顾

现在，我们可以重温上一篇文章中计算所有类别和子类别的销售额占总销售额百分比的示例，为了保留颜色上的筛选器。你可以用 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 指南，你将看到完备的描述。

• CALCULATE 感知当前外部筛选上下文^*并复制一份进入新的筛选上下文。
• 为每个筛选器参数计值，对于参数中的每个条件，生成对应列的有效值列表。
• 如果有两个或更多筛选器参数影响同一列时，使用 AND 运算符将它们合并(或者用数学术语描述：使用交集)。
• 使用新的筛选条件替换当前列已有的筛选器。即，对于那些已经存在筛选器的列，新的筛选器将替换掉现有筛选器。如果当前列不存在任何筛选器，DAX 直接施加新的筛选器。
• 一旦得到了新的筛选上下文，CALCULATE 就在此上下文中计值第一参数（表达式），计值完成后恢复初始筛选上下文，返回计算结果。

筛选器的 and 运算

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 的行会返回结果，其他行返回空值。

布尔表达式不能引用多列

注意：2021 年 3 月开始，Power BI Desktop 已经支持下面这种写法

筛选单个列很简单。而一个不那么明显的事实是，如果希望使用多个条件作为 CALCULATE 的筛选器，一次也只能筛选一列。例如，你可能希望创建一个度量值，只计算单价至少是单位成本两倍的产品的销售额。你可以试试这个公式：

[HighProfitabilitySales] :=
CALCULATE (
    SUM ( Sales[SalesAmount] ),
    Product[Unit Price] >= Product[Unit Cost] * 2  // 21 年 3 月后，Power BI Desktop 已支持这种写法
)

可以看到，这一次，筛选器包含两列：单位成本和单位价格。即使看起来 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 函数一文的测试题中，公式 C 是这么定义的：

CALCULATE (
     COUNTROWS ( Sales ),
     FILTER ( ALL ( Sales ),[Order Date] <= DATE ( 2007, 1, 1 ) ),
     FILTER ( ALL ( Sales ),[Delivery Date] >= DATE ( 2006, 1, 1 ) )
)

这里使用了多个并列的 FILTER 参数，你已经知道这种写法相比于公式 A 和 B，性能稍差，虽然差距只在几毫秒，但理解背后的原理对于优化复杂模型仍有十分重要的价值：

复杂筛选器扩展

本节没有原配的案例，为了方便大家练习，你可以从下方下载权威指南的示例文件自己练习