DAX权威指南3：变量、迭代函数与计算组

全书参考《DAX权威指南》

六、变量

6.1 变量的位置和作用域

   VAR 用于定义变量，RETURN用于定义要返回的结果。一个VAR/RETURN块中可以定义多个变量，但RETURN只能有一个。

  变量的定义位置比较灵活。由于VAR/RETURN 块是一个完整的表达式，所以你可以将变量定义在最前面，然后整段引用：

VAR SalesAmt =SUMX (Sales,Sales[Quantity] * Sales[Net Price])
RETURNIF (SalesAmt > 100000,SalesAmt,SalesAmt * 1.2)

也可以嵌套在其它表达式中，例如：

VAR SalesAmt =
SUMX (Sales,VAR Quantity = Sales[Quantity]VAR Price = Sales[Price]RETURNQuantity * Price)
RETURN ...

  在一个 VAR/RETURN 块中定义的变量，其作用范围是整个 VAR/RETURN 块（VAR定义开始，到RETURN语句为止），变量不能在其所在的VAR/RETURN块之外使用。比如以上在SUMX迭代器中定义的变量，只能在该迭代器内部使用，不能在迭代器外面使用。同理，变量可以引用之前已经定义好的变量，但不能引用尚未定义的变量。

  嵌套VAR/RETURN块中，内层可以引用外层定义的变量，反之则不行。例如，以下代码中，LineAmount在内层VAR/RETURN块中仍然可以被访问：

Margin :=
SUMX (Sales,VAR LineAmount = Sales[Quantity] * Sales[Net Price]RETURN (LineAmount- VAR LineCost = Sales[Quantity] * Sales[Unit Cost]RETURN (LineCost -- Here LineAmount is still accessible))
)

6.2 变量的类型和一般用法

6.2.1 变量的一般用法

  变量既可以是一个标量值，也可以是一张表。在同一个VAR/RETURN块中，可以定义不同类型的变量。可以将复杂的计算表达式按照逻辑拆分，把每一步的结果分配给一个变量，这样可以使表达式更清晰易读。比如以下表达式没有使用变量，显得非常复杂：

Margin% :=
DIVIDE (SUMX (Sales,Sales[Quantity] * Sales[Net Price]) - SUMX (Sales,Sales[Quantity] * Sales[Unit Cost]),SUMX (Sales,Sales[Quantity] * Sales[Unit Cost])
)

使用表达式，结构更清晰：

Margin% :=
VAR SalesAmount =SUMX ( Sales, Sales[Quantity] * Sales[Net Price] )
VAR TotalCost =SUMX ( Sales, Sales[Quantity] * Sales[Unit Cost] )
VAR Margin = SalesAmount - TotalCost
VAR MarginPerc =DIVIDE ( Margin, TotalCost )
RETURNMarginPerc

  此外，还有个好处是，变量在计算过程中只被计算一次，即使它被用在多个地方。比如此例中的TotalCost被用在了两个不同的部分，但由于它被定义为一个变量，所以只计算一次，提高效率。

  RETURN后面可以写任何表达式，但是在RETURN后面只用一个变量被认为是最佳实践。比如本例中返回MarginPerc，而不是返回DIVIDE ( Margin, TotalCost )。因为使用单个变量作为RETURN的结果，可以在调试和优化代码时，方便地检查中间步骤的计算结果。如果怀疑某个中间步骤的计算结果有问题，可以通过替换RETURN后面的变量来逐步检查：

• 替换MarginPerc为Margin：

  RETURNMargin
  

• 替换为TotalCost：

  RETURNTotalCost
  

• 替换为SalesAmount：

  RETURNSalesAmount
  

6.2.2 变量的嵌套定义

  变量可以被定义为一个表达式，该表达式中可以进一步定义其他变量。例如，以下代码中，在外层定义了变量CurrentPrice，但具体的计算逻辑是在内层SUMX中完成的。

Amount at Current Price :=
SUMX ( 'Product',VAR CurrentPrice = 'Product'[Unit Price]RETURN -- CurrentPrice is available within the inner SUMXSUMX (RELATEDTABLE ( Sales ),VAR Quantity = Sales[Quantity] VAR AmountAtCurrentPrice = Quantity * CurrentPriceRETURNAmountAtCurrentPrice)-- Any reference to Quantity, or AmountAtCurrentPrice-- would be invalid outside of the innermost SUMX
)
-- Any reference to CurrentPrice 
-- would be invalid outside of the outermost SUMX

6.2.3 表变量

如果用来定义变量的表达式是一个表的表达式，那么变量包含了这张表：

Amount :=
IF ( HASONEVALUE ( Slicer[Factor] ),VAR Factor = VALUES ( Slicer[Factor] )RETURN DIVIDE ([Sales Amount],Factor)
)

  VALUES是一个表函数，它通常返回一个表。然而，当VALUES函数的结果只包含单个值时，DAX会自动将其视为标量值。本例中，如果Slicer[Factor]在当前筛选上下文中只有一个值，那么Factor返回的是一个标量。如果没有使用HASONEVALUE检查单个行的存在，变量在DIVIDE的第二参数中使用时会出错，因为变量无法正确地从表转换为标量值。

  表变量的迭代：定义一张表，通常是需要在这张表上进行迭代。迭代时，列引用必须使用原始表的名字来引用列，而不是变量的名字。例如下面的表达式中，MultiSales[Quantity]引用是无效的：

Filtered Amount :=
VAR MultiSales = FILTER ( Sales, Sales[Quantity] > 1 )
RETURN SUMX ( MultiSales,-- MultiSales is not a table name for column references-- Trying to access MultiSales[Quantity] would generate an errorSales[Quantity] * Sales[Net Price] )

  变量命名规则：在DAX中，变量名不能与模型中的任何表名相同，这一规则有助于区分变量引用和表引用，从而减少混淆。例如，如果你有一个名为Sales的表，那么不能将其定义为变量名：

SUMX (LargeSales,Sales[Quantity] * Sales[NetPrice]
)

  尽管这种命名限制在短期内减少了混淆，但从长远来看可能会成为一个问题。因为这会限制你的命名选择，还必须考虑未来冲突的风险。为了避免变量名与表名冲突的问题，Power BI在生成DAX查询时，会使用带有两个下划线__的预置变量名称，例如__x，因为用户不太可能使用这种特殊的命名方式。

  现版本变量名可以和表名相同，此时最好使用单引号进行区分。即VarName表示变量，'tableName'表示表。例如，以下代码是OK的：

Margin% = 
VAR SalesAmount =SUMX ( 'Sales', 'Sales'[Quantity] * 'Sales'[Net Price] )
VAR TotalCost =SUMX ( 'Sales', 'Sales'[Quantity] * 'Sales'[Unit Cost] )
VAR Margin = SalesAmount - TotalCost
VAR Sales =DIVIDE ( Margin, TotalCost )
RETURNSales

6.3 变量的特性

6.3.1 常量特性

  尽管VAR叫做变量，但其实它是一个不变的量（常量）：变量是根据其定义的上下文进行计算的，且在定义范围内只会被计算一次，之后调用时保持不变（直接引用）。即使后面调用时使用CALCULATE函数修改上下文，也不会因为上下文的变化而重新计算。

% of Product :=
VAR SalesAmount = SUMX ( Sales, Sales[Quantity] * Sales[Net Price] )
RETURNDIVIDE (SalesAmount,CALCULATE (SalesAmount,ALL ( 'Product' )))

  此例中， SalesAmount 变量不被 CALCULATE 所影响，它的值仅仅被计算一次，所以两次计算的SalesAmount值相同，并不能得到想要的结果。正确的做法是引用度量值：

Sales Amount := SUMX ( Sales, Sales[Quantity] * Sales[Net Price] )
% of Product :=
DIVIDE ([Sales Amount],CALCULATE ([Sales Amount],ALL ( 'Product' )))

此例中，SalesAmount 在两个不同的筛选上下文中被执行，自然就导致了两个不同的结果。

6.3.2 延迟计算

  根据上一节可知，变量仅会在其被定义的上下文中执行一次，并在后续调用中保持不变。然而，变量的计算会被延迟到它第一次被使用时，而不是被定义时，这种行为被称为延迟计算。延迟计算对于性能非常重要，这意味着不会浪费宝贵的CPU时间来计算未使用的变量。例如：

Sales Amount :=
VAR SalesAmount = SUMX ( Sales, Sales[Quantity] * Sales[Net Price] )
VAR DummyError = ERROR ( "This error will never be displayed" )
RETURN SalesAmount

DummyError变量包含一个错误表达式，但由于它从未被使用，因此永远不会被执行。

6.4 VAR的常见模式和使用场景

6.4.1 拆分代码逻辑，提升代码可读性

  以下代码通过定义LargeCustomers和WorkingDaysIn2008两个变量，将复杂的筛选条件分解，使代码更易于理解和维护。

Sales Large Customers :=
VAR LargeCustomers = FILTER ( Customer,[Sales Amount] > 10000)
----将大客户的筛选条件定义为销售量大于 1 万
VAR WorkingDaysIn2008 = CALCULATETABLE ( ALL ( 'Date'[IsWorkingDay], 'Date'[Calendar Year] ),'Date'[IsWorkingDay] = TRUE (),'Date'[Calendar Year] = "CY 2008")
----将在 2008 年中属于工作日的日期筛选出来，形成一个日期表
RETURN CALCULATE ([Sales Amount],LargeCustomers,WorkingDaysIn2008)
----将定义的两个筛选条件并入 Calculate 函数，计算大客户（在 2008 年工作日购买数量大于 10000) 的采购金额

6.4.2 处理嵌套行上下文（代替EARLIER函数）

  假设要计算每种产品累计销量，那就需要筛选出当前行及之前的行的所有相同产品。一种方法是使用EARLIER访问外层循环：

产品累计销量 =
CALCULATE (SUM ( '订单表'[销售数量] ),'订单表'[序号] <= EARLIER ( '订单表'[序号] ),&&'订单表'[产品名称]=EARLIER('订单表'[产品名称]))
)

更清晰易懂的方式是使用VAR来存储和访问外层循环：

VAR Index = [序号]
VAR ProductID = [产品名称]
RETURNCALCULATE (SUM ( [销售数量] ),FILTER ( '订单表', [序号] <= Index && [产品名称] = ProductID ))

6.4.3 处理嵌套筛选上下文

  假设我们有一个Customer表，其中包含每个客户的销售金额（[Sales Amount]）。我们想要计算销售金额大于平均销售金额的客户数量。首先，我们来看一个错误的实现方式：

AverageSalesPerCustomer := 
AVERAGEX ( Customer, [Sales Amount] )CustomersBuyingMoreThanAverage :=
COUNTROWS (FILTER (Customer, [Sales Amount] > [AverageSalesPerCustomer])
)

CustomersBuyingMoreThanAverage :=
COUNTROWS (FILTER (Customer, [Sales Amount] > [AverageSalesPerCustomer])
)

  AverageSalesPerCustomer度量值用于计算所有客户销售金额的平均值。在《DAX权威指南2：CALCULATE 与 CALCULATETABLE》的4.4章节中讲过，每个度量值引用总是伴随着一个隐式的 CALCULATE调用，即 [AverageSalesPerCustomer]总是等价于CALCULATE ( [AverageSalesPerCustomer] )，所以上式中的FILTER函数等价于：

FILTER (Customer, [Sales Amount] > CALCULATE ( AVERAGEX ( Customer, [Sales Amount] )))

  当在迭代函数FILTER 中使用CALCULATE时，会触发隐式的上下文转换，将当前行上下文（当前行的客户）转换为筛选上下文。此时，[AverageSalesPerCustomer] 的计算会被限制为当前迭代的客户，而非所有客户（由于上下文转换）。这导致 AVERAGEX 实际计算的是当前客户的销售额平均值（即其自身销售额）。此时比较 [Sales Amount] > [AverageSalesPerCustomer] 等价于判断 X > X，结果恒为 false，始终返回空。

一种解决方式是通过 Variables 提前捕获全局平均值。只需要在外部进行一次，就可以被反复调用，大幅优化性能：

AverageSalesPerCustomer := 
AVERAGEX ( Customer, [Sales Amount] )			// 提前计算并存储全局平均值CustomersBuyingMoreThanAverage :=
COUNTROWS (FILTER (Customer, [Sales Amount] > [AverageSalesPerCustomer]	// 直接与固定值比较)
)

也可以修改 AverageSalesPerCustomer，使其计算所有客户的平均销售额，忽略当前筛选上下文（不推荐）：

AverageSalesPerCustomer := 
CALCULATE (AVERAGEX ( Customer, [Sales Amount] ),ALL ( Customer )  -- 移除对Customer表的筛选
)

6.4.4 使用DAX Studio编写DAX公式

  Variables 函数可以编辑更有可读性、性能更优良和更优雅的代码。每当需要编写复杂公式时，将其拆分为多个变量是一种推荐的做法。当表达式长度超过 10 行的时候，用于编写 DAX 代码的用户界面对编写过程很不友好（比如Power BI的编辑栏），特别是要添加一些注释。最好是使用 DAX Studio 等外部工具来创作较长的 DAX 代码。

七、迭代器

  迭代器是 DAX 中一类特殊的函数，它们至少需要两个参数：一个用于迭代的表和一个在迭代期间逐行计算的表达式。前者在迭代开始之前就已经计算完成，后者依赖于行上下文。迭代器会逐行计算表达式，并根据不同的函数对结果进行聚合。

  以 SUMX 函数为例，它是最常用的迭代器之一，用于对表中的每一行计算表达式并求和：

Sales Amount := 
SUMX ( Sales, -- 用于被迭代的表 Sales[Quantity] * Sales[Net Price] -- 逐行计算的表达式
)

  官方 Microsoft 文档未提供迭代器函数的准确分类，在网站 https://dax.guide 上，所有带有标记为 ROW CONTEXT（行上下文） 的参数的函数都是迭代器，例如：

7.1 迭代器基数

7.1.1 单个迭代器的基数

  迭代器的基数就是被迭代表的行数。例如，在以下迭代器代码中，如果 Sales 表有一百万行，则基数为一百万。

Sales Amount :=
SUMX (Sales, -- 如果,Sales 表有 100 万行Sales[Quantity] * Sales[Net Price] -- 表达式会被执行 100 万次
)

  在谈论基数时，我们很少使用数字，因为具体的基数取决于 Sales 表的行数。所以，一般会说迭代器的基数与 Sales 的基数相同（类似计算复杂度）。

7.1.2 嵌套迭代器的基数与执行计划

  基数的大小直接影响迭代器的性能，基数越大，迭代的次数就越多。嵌套迭代器的基数是两个迭代器基数的组合，最大可以是两者的乘积。

Sales at List Price 1 :=
SUMX ('Product',SUMX (RELATEDTABLE ( Sales ),'Product'[Unit Price] * Sales[Quantity])
)

  这个例子中有两个迭代器。外部迭代器 Product 表。则它的基数是 Product 表的基数。内部迭代器迭代Sales 表，但是只计算Sales 表中与给定产品具有关系的行。因为 Sales 表中的每一行只与一个产品相关，所以迭代器完整基数还是 Sales 表的基数。

  内部表表达式与外部表表达式无关时，基数会变得更高。以下代码，其功能与上述代码相同，但是不使用依赖关系过滤，而是使用 IF 函数来过滤。

Sales at List Price High Cardinality :=
SUMX (VALUES ( 'Product' ),SUMX (Sales,IF (Sales[ProductKey] = 'Product'[ProductKey],'Product'[Unit Price] * Sales[Quantity],0))
)

嵌套迭代器的执行计划：

1. 外层迭代器 VALUES ( 'Product' ) 会生成一个临时表，存储 Product 表的每一行。
2. 对于临时表中的每一行，内层迭代器 SUMX ( Sales, ... ) 会完整地扫描 Sales 表。
3. 每次扫描 Sales 表时，都会计算 'Product'[Unit Price] * Sales[Quantity]，并根据条件过滤。
4. 最终结果是将所有计算结果累加。

  可以看出，内部的 SUMX 函数始终遍历整个 Sales 表，逐行判断是否要进行计算。这种情况下，整个表达式的基数是 Product 表行数乘以 Sales 表行数，导致性能下降。以上代码仅用于教学示例，更好的处理方法如下，避免了嵌套迭代器，性能更好：

Sales at List Price 2 :=
SUMX (Sales,RELATED ( 'Product'[Unit Price] ) * Sales[Quantity]
)

7.1.3 隐藏的嵌套迭代

下面一个是简单的度量公式，单看此公式可能会认为只有一个迭代器：

Sales at List Price 3 :=
SUMX ('Product','Product'[Unit Price] * [Total Quantity]
)

公式中引用了Total Quantity度量，其定义为：

Total Quantity := SUM ( Sales[Quantity] ) 
-- 在 DAX 内部会将此行代码转换为
-- SUMX ( Sales, Sales[Quantity] )

任何时候对度量值的引用都伴随一个隐式的CALCULATE函数，所以展开之后有：

Sales at List Price 4 :=
SUMX ('Product','Product'[Unit Price] * CALCULATE ( SUMX ( Sales, Sales[Quantity] ) )
)

  很明显可以看到，这是一个嵌套迭代——内外层迭代器（SUMX）分别对 Sales 表和Product 表进行迭代，另外因为 CALCULATE 函数的存在，还会发生上下文转换。

7.1.4 嵌套迭代的限制

1. 性能问题
   在嵌套迭代器中，查询计划只能优化最内层的迭代器。外层迭代器需要在内存中创建临时表来存储中间结果。这些临时表会占用大量内存，尤其是当外层迭代器的基数非常大（如数百万行）时，性能问题会更加明显。在可能的情况下，使用 RELATED 函数直接访问相关表的字段，而不是通过嵌套迭代器来过滤数据。

2. 上下文转换问题：在迭代器中引用度量时需要小心，这可能会因为上下文转换导致错误。

   Sales at List Price 5 :=
   SUMX ('Sales',RELATED ( 'Product'[Unit Price] ) * [Total Quantity]
   )
   

   Sales at List Price 5看起来和Sales at List Price 3功能相同，但实际上存在很大问题：

   • 在大型表上执行上下文转换：Sales 表是一个大型表，执行上下文转换会显著降低性能，因为上下文转换非常昂贵，它会将所有列的值都添加到筛选上下文中
   • 非唯一行的问题：如果表中的行不是唯一的，上下文转换可能会筛选出多行，而不是预期的单行。
     Sales at List Price 3中对Product 表进行迭代，Product 表有一个唯一键列Productkey，因此每一行都是唯一的，由上下文转换生成的筛选上下文保证只筛选一个产品。而Sales at List Price 5在Sales 表中进行迭代，由于Sales 表中的行不是唯一的，上下文转换可能会筛选出多行。在许多实际场景中，表中重复的行数很少，这些不准确的计算很难被发现并改正。
     

综上所述，Sales at List Price 5不仅计算缓慢，而且结果也是错误的。

嵌套迭代器并非总是有害的，它在某些情况下是一个强大的工具，但是使用不当也会导致性能问题和计算错误。建议：

• 优化查询计划：在可能的情况下，尽量优化最内层迭代器的查询计划，以减少性能损失。
• 避免在大型表上使用嵌套迭代器
• 在迭代器中引用度量值要谨慎，避免在非行唯一表上执行上下文转换。

7.2 利用迭代器中的上下文转换

7.2.1 计算每月最大销售额

  在某些情况下，通过合理利用上下文转换，我们可以避免嵌套迭代器，从而减少计算复杂度和内存消耗。假设我们需要计算一段时间内每日最大销售额。一种直观的方法是先计算每个日期的销售额，然后在这些结果中找到最大值：

Max Daily Sales 1 :=
MAXX ('Date',VAR DailyTransactions =RELATEDTABLE ( Sales )VAR DailySales =SUMX ( DailyTransactions, Sales[Quantity] * Sales[Net Price] )RETURNDailySales
)

上述代码使用了嵌套迭代器来实现，更简单的方法是引用 Sales Amount度量，通过隐式上下文转换来解决问题：

Sales Amount :=
SUMX ( Sales, Sales[Quantity] * Sales[Net Price] )Max Daily Sales 2 :=
MAXX ( 'Date', [Sales Amount] )

  ，Date 表通常包含几百行记录，因此外部迭代器的基数较小；Date 表中的每一行是唯一的，也不会产生不必要的重复计算，因此这两种方法是安全的（不会导致性能问题）且快速的。

  第一张方法，更完整地展示了算法的实现细节。第二种方法，[Sales Amount] 被用在 Date 表的上下文中，通过隐式的上下文转换，将过滤器从 Date 表传递到 Sales 表，避免了显式的嵌套迭代，代码更加优雅简洁，但隐藏了内部迭代的细节。两种方法都能得到正确的结果.。

7.2.2 找到最大销售额对应的日期

除了计算最大销售额，我们还可能希望找到最大销售额对应的日期。这可以通过以下代码实现：

Date of Max =
VAR MaxDailySales = [Max Daily Sales]
VAR DatesWithMax =FILTER ( VALUES ( 'Date'[Date] ), [Sales Amount] = MaxDailySales )
VAR Result =IF ( COUNTROWS ( DatesWithMax ) = 1, DatesWithMax, BLANK () )
RETURNResult

  在这个例子中，MaxDailySales 是最大销售额的值，DatesWithMax 是一个临时表，包含所有销售额等于 MaxDailySales 的日期。如果只有一个日期满足条件，则返回该日期，否则返回空白。

7.2.3 迭代器的一般使用流程

在 DAX 中使用迭代器时，建议按照以下清晰的顺序定义计算逻辑：

1. 确定计算的粒度：即你希望在哪个维度或层级上进行计算。例如，是按日期、客户、产品，还是其他维度。
2. 定义要计算的表达式：即在选定的粒度上，你需要计算的具体内容。例如，销售额、销售数量或其他指标。
3. 选择合适的聚合类型：即你希望如何汇总计算结果，例如求和（SUM）、平均（AVERAGE）、最大值（MAX）等。

  通过遵循这种结构化的定义方式，可以确保你的 DAX 公式清晰、高效且易于维护。在前面提到的 Max Daily Sales 2 示例中，粒度是日期（按日期进行计算），表达式是销售额，要使用的聚合是 MAX（找到销售额中的最大值），最终结果是每日最高销售额。

  同样的模式也可以应用于其他场景。例如，如果我们想计算每个客户的平均销售额，那么粒度是单个客户，要使用的表达式是销售额，聚合是 AVERAGE 函数，最终公式如下：

Avg Sales by Customer :=
AVERAGEX ( Customer, [Sales Amount] )

通过这个简单的公式，我们可以轻松构建强大的报告，例如按洲和年份显示每个客户的平均销售额。

7.3 CONCATENATEX 函数

7.3.1 语法

CONCATENATEX 函数用于将表中的值连接成一个字符串，并允许指定分隔符。它在迭代表中的每一行时，计算指定的表达式，并将结果连接起来。其基本语法如下：

CONCATENATEX (<Table>,               -- 要迭代的表<Expression>,          -- 每行计算的表达式<Delimiter>,           -- 用于分隔每个值的字符串[<OrderBy_Expression>], -- 可选：排序依据[<Order>]              -- 可选：排序方向（ASC 或 DESC）
)

7.3.2 显示用户选择的过滤器值

  在 Power BI 报表中，用户可以通过切片器选择多个值。如果切片器位于不同的页面中，则不太清楚到底选择了哪些值。为了在报表中动态显示用户的选择，CONCATENATEX 函数可以将这些选择的值连接成一个字符串，并以友好的方式展示给用户。

Selected Colors :=
"Showing " &
CONCATENATEX (VALUES ( 'Product'[Color] ), -- 迭代颜色表'Product'[Color],           -- 每行计算的颜色值", ",                       -- 用逗号分隔'Product'[Color], ASC       -- 两个参数，表示按颜色升序排序
) & " colors."					-- 末尾添加后缀字段colors.

在实际使用中，直接使用 CONCATENATEX 可能会导致一些问题，例如：

• 如果用户没有选择任何值，则会显示所有可能的值，导致列表过长。
• 如果用户选择的值过多，返回的字符串过长，影响用户体验。

为了优化用户体验，对代码进行优化：如果用户选择了所有可能的值，则显示“Showing all colors.”；如果用户选择的值过多，则提醒用户：

Selected Colors :=
VAR Colors =VALUES ( 'Product'[Color] )
VAR NumOfColors =COUNTROWS ( Colors )
VAR NumOfAllColors =COUNTROWS ( ALL ( 'Product'[Color] ) )
VAR AllColorsSelected = NumOfColors = NumOfAllColors
VAR SelectedColors =CONCATENATEX ( Colors, 'Product'[Color], ", ", 'Product'[Color], ASC )
VAR Result =IF (AllColorsSelected,"Showing all colors.",IF (NumOfColors > 5,"More than 5 colors selected, see slicer page for details.","Showing " & SelectedColors & " colors."))
RETURNResult

  后一版还是有些问题，当用户选择了五种颜色，但由于其他过滤器的作用，某些颜色在当前上下文中被隐藏，导致实际显示的颜色只有四种时，这个度量值无法正确报告完整的颜色列表，而只能显示当前上下文中的颜色列表。
  为了完全解决这个问题，需要进一步完善度量值的逻辑。这需要引入一些新的函数，这些函数能够调查当前的过滤器上下文内容，从而更准确地处理和报告用户的选择。这些内容将在后续章节（第10章“使用过滤器上下文”）中详细描述。

7.4 返回表的迭代器函数

  ADDCOLUMNS 和 SELECTCOLUMNS 函数用于在迭代过程中动态生成新表，这些函数允许用户在现有表的基础上添加或选择列，并在行上下文中计算新列的值。

7.4.1 ADDCOLUMNS 函数（添加列）

ADDCOLUMNS 函数用于在现有表的基础上添加新列。它接受一个表作为输入，并为该表的每一行计算新列的值。其基本语法如下：

ADDCOLUMNS (<Table>, -- 原始表"<NewColumnName1>", <Expression1>, -- 新列名称和表达式"<NewColumnName2>", <Expression2>, -- 可以添加多个新列...
)

例如，您可以在颜色列表中添加两列，分别计算产品数量和销售额。

Colors =
ADDCOLUMNS (VALUES ( 'Product'[Color] ), 						-- 原始表：产品颜色的唯一值"Products", CALCULATE ( COUNTROWS ( 'Product' ) ),  -- 新列：每种颜色的产品数量"Sales Amount", [Sales Amount] 						-- 新列：每种颜色的销售额
)

7.4.2 SELECTCOLUMNS 函数（选择列）

  ADDCOLUMNS 函数会返回其迭代的表的所有原始列，并在这些列的基础上添加新的列。如果你只想保留原始表中的部分列，那么可以使用 SELECTCOLUMNS 函数，并可以为这些列指定新的名称，其基本语法如下：

SELECTCOLUMNS (<Table>, -- 原始表"<NewColumnName1>", <Expression1>, -- 新列名称和表达式"<NewColumnName2>", <Expression2>, -- 可以选择多个列...
)

例如上一节的代码可改写成：

Colors =
SELECTCOLUMNS (VALUES ( 'Product'[Color] ), 		-- 原始表：产品颜色的唯一值"Color", 'Product'[Color], 			-- 选择原始表中的颜色列"Products", CALCULATE ( COUNTROWS ( 'Product' ) ), -- 新列：每种颜色的产品数量"Sales Amount", [Sales Amount] 		-- 新列：每种颜色的销售额
)

结果是相同的，但您需要显式包含原始表的 Color 列以获得相同的结果。

7.4.3 使用中间表存储复杂计算

通过将复杂的计算结果存储在表中，可以避免重复计算，优化性能。假设我们需要计算每日最大销售额及其对应的日期，原始代码如下：

Max Daily Sales :=
MAXX ( 'Date', [Sales Amount] )Date of Max :=
VAR MaxDailySales = [Max Daily Sales]
VAR DatesWithMax =FILTER ( VALUES ( 'Date'[Date] ), [Sales Amount] = MaxDailySales )
VAR Result =IF ( COUNTROWS ( DatesWithMax ) = 1, DatesWithMax, BLANK () )
RETURNResult

  在这个版本中，[Sales Amount] 在 MAXX 和 FILTER 中重复计算，即引擎在 Date 表上执行两次迭代。DAX 优化器可能会检测这一点，只进行一次计算，但不能保证这一点。最好是将复杂的计算结果存储在表中，以避免重复计算：

Date of Max :=
VAR DailySales =ADDCOLUMNS ( VALUES ( 'Date'[Date] ), "Daily Sales", [Sales Amount] )
VAR MaxDailySales =MAXX ( DailySales, [Daily Sales] )
VAR DatesWithMax =SELECTCOLUMNS (FILTER ( DailySales, [Daily Sales] = MaxDailySales ),"Date", 'Date'[Date])
VAR Result =IF ( COUNTROWS ( DatesWithMax ) = 1, DatesWithMax, BLANK () )
RETURNResult

• DailySales：通过 ADDCOLUMNS 预先计算每个日期的销售额。
• MaxDailySales：从 DailySales 表中计算最大销售额。
• DatesWithMax：从 DailySales 表中筛选出最大销售额对应的日期。

7.5 计算平均值和移动平均值

7.5.1 使用AVERAGEX

  在业务分析中，计算平均值是常见的需求。DAX 提供了多种函数来计算平均值，包括 AVERAGE 和 AVERAGEX。AVERAGE 函数直接计算数字列的平均值，而 AVERAGEX 则可以通过迭代表中的每一行来计算更复杂的平均值。

  假设我们要分析 Contoso 的每日销售额，以天为粒度，由于波动太大，结果很难分析出结果。为了平滑图表，一种常见的方式是使用移动平均值，比如计算过去 30 天的销售平均值：

AvgXSales30 :=
VAR LastVisibleDate = MAX ( 'Date'[Date] )
VAR NumberOfDays = 30
VAR PeriodToUse =FILTER (ALL ( 'Date' ),AND ('Date'[Date] > LastVisibleDate - NumberOfDays,'Date'[Date] <= LastVisibleDate))
VAR Result =CALCULATE (AVERAGEX ( 'Date', [Sales Amount] ) ,PeriodToUse)
RETURNResult

  这段代码首先确定最后一个可见日期，然后创建一个包含过去 30 天的日期范围，最后计算这个范围内的平均销售额。这种方法可以有效平滑数据，帮助我们更好地分析趋势。

7.5.2 使用DIVIDE

  在某些情况下，我们可能希望将没有销售的日期也考虑进去，并将销售额视为零。由于AVERAGEX 函数会忽略空值，所以可以使用简单的除法来实现这一目标，只需要更改Result部分代码：

AvgSales30 :=
VAR LastVisibleDate =MAX ( 'Date'[Date] )
VAR NumberOfDays = 30
VAR PeriodToUse =FILTER (ALL ( 'Date' ),'Date'[Date] > LastVisibleDate - NumberOfDays&& 'Date'[Date] <= LastVisibleDate)
VAR Result =CALCULATE ( DIVIDE ( [Sales Amount], COUNTROWS ( 'Date' ) ), PeriodToUse )
RETURNResult

  这种方法将没有销售的日期视为零，从而更准确地反映实际的平均销售额，其结果比移动平均值更小。

7.5.3 修改计算的粒度

  示例模型包含一个 Date 表，其中有一列 IsWorkingDay，用于标识某一天是否为工作日（为了简化问题，我们假设只有周六和周日是非工作日，以0表示）。假设需要计算的度量值是“每个工作日的销售额”，即总销售额除以工作日的数量，那么公式为：

NumOfDays := COUNTROWS ( 'Date' )
NumOfWorkingDays := SUM ( 'Date'[IsWorkingDay] )
SalesPerWorkingDay := DIVIDE ( [Sales Amount], [NumOfWorkingDays] )

  可以看到，此公式在月度级别是正确的，但是在年级别或总计级别上是错的。例如，2007 年均值低于任何一个月的均值，这明显是错误的，问题的根本原因在于没有销售额的日期也被算进去了。在月级别BLANK()除以任意整数都是BLANK()，结果正确；在年和总计级别，明显就是错的。

  有多种方法可以改进计算逻辑，一种方式使用 SUMX 函数来迭代每个月，只统计有销售额的月份：

SalesPerWorkingDay :=
VAR WorkingDays =							-- 将所有有销售数据的月份的工作日数量相加，得到总的工作日数量。SUMX ( VALUES ( 'Date'[Month] ), 		-- 迭代每个月份IF ( [Sales Amount] > 0, 				-- 检查每个月是否有销售数据[NumOfWorkingDays] ) )
VAR Result =DIVIDE ( [Sales Amount], WorkingDays )
RETURNResult

上述公式在年级别上可以正常工作，但在总计级别上仍然出现问题，这是因为不同的年份都会有同样的月份（2007-5和2008-5），所以 VALUES ( 'Date'[Month]是有问题的。我们真正需要的是一个包含年份和月份组合的列，模型中的 Calendar Year Month 列可以满足这个需求：

SalesPerWorkingDay :=
VAR WorkingDays =SUMX (VALUES ( 'Date'[Calendar Year Month] ),IF ( [Sales Amount] > 0, [NumOfWorkingDays] ))
VAR Result =DIVIDE ( [Sales Amount], WorkingDays )
RETURNResult

  在 DAX 中，正确处理计算的粒度是确保结果准确性的关键。通过使用迭代器（如 SUMX）和合适的粒度列（如 Calendar Year Month），我们可以解决在不同粒度级别上可能出现的问题。

7.6 RANKX

7.6.1 基本语法与用法

RANKX 是一个迭代器函数，用于根据特定的排序顺序计算排名，其基本语法如下：

RANKX (<Table>,			-- 物理表或表表达式<Expression>,		-- 逐行计算的表达式，它的值将用于构建查找表[<Value>],			-- 要排名的值。如果省略，会使用 <Expression>的值作为排名值[<Order>],			-- 排序顺序，可以是 ASC（升序）或 DESC（默认降序）。[<Ties>]			-- 指定如何处理平级情况，默认SKIP，可以选 DENSE
)

假设我们有一个销售数据模型，我们希望根据销售额对产品类别进行排名，以下是一个简单的示例：

Rank Cat on Sales :=
RANKX (ALL ( 'Product'[Category] ),[Sales Amount]
)

RANKX 函数计算过程：

• 构建查找表：RANKX 在迭代 ALL ( 'Product'[Category] ) 表时，计算每个类别的 [Sales Amount]，构建一个查找表（查找表仅包含值）。
• 查找表排序：查找表根据 [Sales Amount] 的值进行降序排序（默认行为）。
• 排名计算：RANKX 在原始计算上下文中计算当前类别的 [Sales Amount]，然后在排序后的查找表中查找这个值的位置，确定排名。

默认排名（即 SKIP）与密集排名：

Rank On Rounded Sales :=
RANKX (ALL ( 'Product'[Brand] ),[Rounded Sales]
)
Rank On Rounded Sales Dense :=
RANKX (ALL ( 'Product'[Brand] ),[Rounded Sales],,,DENSE
)

7.6.2 使用HASONEVALUE隐藏总计行并保护代码

上述结果中，总计行排第一是没有意义的，因为总计行排名始终为 1。如果想隐藏它，可以使用 HASONEVALUE 函数来保护度量值：

Rank Cat on Sales :=
IF (HASONEVALUE ( 'Product'[Category] ),		-- HASONEVALUE 函数检查指定列是否在当前上下文中只有一个值RANKX (ALL ( 'Product'[Category] ),[Sales Amount])
)

  这个公式确保在当前筛选上下文中只有一个类别时，才会计算排名，否则返回空，避免在总计行中显示无意义的排名值。此外，以上代码是在在按品牌进行切片的报表中使用，如果不小心按颜色进行切片，会导致错误的结果：

  这是因为ALL ( 'Product'[Brand] )忽略当前的筛选上下文，返回一个包含所有品牌的表，这意味着查找表中包含所有品牌的销售额（只有这一个值）。而计算表达式[Sales Amount]在颜色切片中，会计算当前颜色的销售额，怎么排都是第一。

  为了避免这个问题，我们需要确保查找表和当前上下文中的值是一致的。使用 IF HASONEVALUE 函数添加代码保护，可确保 如果计算上下文未过滤单个品牌，结果将是空白的。

7.6.3 动态排名（按所选类别进行排名）

Rank On Selected Brands :=
RANKX (ALLSELECTED ( 'Product'[Brand] ),[Sales Amount]
)Rank On All Brands :=
RANKX (ALL ( 'Product'[Brand] ),[Sales Amount]
)

7.6.4 使用第三个参数，构建查找表

  在某些情况下，我们可能需要使用不同的表达式来构建查找表和计算排名值。例如，假设我们有一个固定的销售排名表，我们希望根据这个表对销售额进行排名：

Rank On Fixed Table :=
RANKX ('Sales Ranking',				-- 固定的销售排名表'Sales Ranking'[Sales],			-- 用于构建查找表的表达式[Sales Amount]					-- 要排名的值
)

计算过程如下：

九、计算组

参考资料《Using calculation groups toselectively replace measures inDAX expressions》、《计算组别》

9.1 计算组简介

9.1.1 问题背景

  在处理时间相关的数据时，我们常常需要进行各种时间智能计算，例如年迄今（YTD）、季度迄今（QTD）、去年同期（PY）等。除此之外，还需要考虑各种指标，例如在一个销售数据模型中，我们可能需要计算销售额、总成本、利润率和总销量等。以下是这些度量值的基本DAX定义：

Sales Amount := SUMX ( Sales, Sales[Quantity] * Sales[Net Price] )
Total Cost := SUMX ( Sales, Sales[Quantity] * Sales[Unit Cost] )
Margin := [Sales Amount] - [Total Cost]
Sales Quantity := SUM ( Sales[Quantity] )

  这些度量值为业务提供了不同的见解，都是有用的。然而，如果为每个度量值都构建不同的时间智能计算，数据模型中的度量值数量可能会迅速增长。例如，年迄今的销售数量、年迄今的销售额和年迄今的销售毛利等计算如下：

YTD Sales Amount := CALCULATE ( [Sales Amount], DATESYTD ( 'Date'[Date] ) )
YTD Total Cost := CALCULATE ( [Total Cost], DATESYTD ( 'Date'[Date] ) )
YTD Margin := CALCULATE ( [Margin], DATESYTD ( 'Date'[Date] ) )
YTD Sales Quantity := CALCULATE ( [Sales Quantity], DATESYTD ( 'Date'[Date] ) )

  这些计算都是基于相同的模式，只是将不同的基本度量值与DATESYTD函数结合。如果能够使用占位符定义一个更通用的计算，那将大大提高效率。

YTD <Measure> := CALCULATE ( <Measure>, DATESYTD ( 'Date'[Date] ) )

9.1.2 计算组的概念

  计算组是一组计算项，它允许我们将一组相关的计算项（Calculation Items）组织在一起。计算项是包含特殊占位符的DAX表达式，在评估结果之前，引擎会将占位符替换为具体的度量值。这样，计算项可以应用于任何度量值，从而减少重复的代码。

例如，我们可以定义一个名为“Time Intelligence”的计算组，其中包含以下计算项：

CALCULATION GROUP "Time Intelligence"
CALCULATION ITEM CY := <Measure>
CALCULATION ITEM PY := CALCULATE ( <Measure>, SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
CALCULATION ITEM QTD := CALCULATE ( <Measure>, DATESQTD ( 'Date'[Date] ) )
CALCULATION ITEM YTD := CALCULATE ( <Measure>, DATESYTD ( 'Date'[Date] ) )

  在这个计算组中，<Measure>是一个占位符，它将被替换为具体的度量值。这样，我们就可以将时间智能计算应用于任何度量值，而无需为每个度量值单独编写代码。

  接着，我们创建一个名为 Metric 的计算组，包括 Sales Amount, Total Cost, Margin, 和Sales Quantity 四个计算项，每个计算项的表达式只计算相应的度量：

Calculation Item: Margin
[Margin]Calculation Item: Sales Amount
[Sales Amount]Calculation Item: Sales Quantity
[Sales Quantity]Calculation Item: Total Cost
[Total Cost]

  通过使用计算组，可以减少重复的度量值定义，构建更加灵活和高效的数据模型。在实际应用中，计算组不仅可以用于时间智能计算，还可以用于其他类型的计算，例如货币汇率转换、单位转换等。

9.1.3 使用计算组

  在 Power BI 等报表工具中，计算组在“数据”窗格中显示为一个表，其中包含一个或多个计算项 。通常会将计算组的列拖拽到可视化效果的“列”区域或用作切片器，以动态地将相应的计算项应用于已添加到“值”区域的度量值。

• 切换到报表视图，创建一个矩阵可视化组件。
• 将日期表（Date table）中的月份列（Month column）添加到行区域。
• 时间智能计算组中的时间计算（Time Calculation）添加到列区域
• 新建订单度量值（“Sales Order”表中不同销售订单的数量），Orders = DISTINCTCOUNT('Sales Order'[Sales Order])，将其添加到值区域。

你也可以通过将计算组列添加到切片器（Slicer）可视化中，将单个计算项应用于多个度量值。通过这种方式，用户可以在报告中轻松地切换不同的时间智能计算，而无需手动修改度量值的定义。

  在度量值中使用计算项：可以使用 DAX 表达式来利用特定度量值上的计算项。例如，要创建一个“订单年同比百分比”（Orders YOY%）度量值，可以使用以下 DAX 表达式：

Orders YOY% = CALCULATE([Orders],'Time Intelligence'[Time Calculation] = "YOY%")

9.2 创建计算组

9.2.1 创建计算组

  在 Power BI Desktop的模型视图中，选择功能区中的计算组按钮 ，如果“阻止隐式度量值”属性未启用，系统会提示启用 。启用后，数据窗格中的数据列将不再显示求和符号，并且无法直接将数据列拖到可视化组件的聚合轴或值字段中。不过，已经创建的隐式度量仍然可以正常工作。

  显式度量值即通过使用 DAX 公式（例如 SUM, AVERAGE）明确创建的度量值。隐式度量值 (Implicit Measures) 是在报表视图中，用户直接将数据列拖拽到可视化效果并进行聚合时，Power BI 自动创建的度量值。计算组只能应用于模型中已存在的显式 DAX 度量值，不能直接应用于隐式度量值（自动度量值）。

创建后，可以在 DAX 公式栏中为第一个计算项定义 DAX 表达式，通常使用 SELECTEDMEASURE() 函数作为基础度量值的占位符。

  如果你不想让计算项改变某些度量值，你可以通过度量值名称，指定计算项忽略这个显式度量。Visual Studio with Analysis Services Projects、表格模型脚本语言 (TMSL) 和 Tabular Editor 也支持创建计算组。

9.2.2 常用时间智能计算项与时间智能查询

关键 DAX 函数	描述
SELECTEDMEASURE()	在计算项的 DAX 表达式中，引用当前上下文中的基础度量值
SELECTEDMEASURENAME()	返回当前上下文中基础度量值的名称
ISSELECTEDMEASURE(<度量值名称>[, <其他度量值名称>])	判断当前上下文中的基础度量值是否在指定的列表中
SELECTEDMEASUREFORMATSTRING()	返回当前上下文中基础度量值的格式字符串

时间智能计算项（优先级20）	函数描述	DAX 表达式
Current	当前时间段的值	SELECTEDMEASURE()
MTD (Month to Date)	月初至今的累计值	MTD=CALCULATE(SELECTEDMEASURE(), DATESMTD('Date'[Date]))
QTD (Quarter to Date)	季度初至今的累计值	QTD=CALCULATE(SELECTEDMEASURE(), DATESQTD('Date'[Date]))
YTD (Year to Date)	年初至今的累计值	YTD=CALCULATE(SELECTEDMEASURE(), DATESYTD('Date'[Date]))
PY (Previous Year)	去年同期的值	PY=CALCULATE(SELECTEDMEASURE(), SAMEPERIODLASTYEAR('Date'[Date]))

PY MTD =
CALCULATE(SELECTEDMEASURE(),SAMEPERIODLASTYEAR('Date'[Date]),'Time Intelligence'[Time Calculation] = "MTD"
)PY QTD =
CALCULATE(SELECTEDMEASURE(),SAMEPERIODLASTYEAR('Date'[Date]),'Time Intelligence'[Time Calculation] = "QTD"
)
PY YTD =
CALCULATE(SELECTEDMEASURE(),SAMEPERIODLASTYEAR('Date'[Date]),'Time Intelligence'[Time Calculation] = "YTD"
)YOY =
SELECTEDMEASURE() -
CALCULATE(SELECTEDMEASURE(),'Time Intelligence'[Time Calculation] = "PY"
)YOY% =
DIVIDE(CALCULATE(SELECTEDMEASURE(),'Time Intelligence'[Time Calculation]="YOY"),CALCULATE(SELECTEDMEASURE(),'Time Intelligence'[Time Calculation]="PY")
)

时间智能查询返回应用的每个计算项的计算表：

EVALUATE
CALCULATETABLE (SUMMARIZECOLUMNS (DimDate[CalendarYear],DimDate[EnglishMonthName],"Current", CALCULATE ( [Sales], 'Time Intelligence'[Time Calculation] = "Current" ),"QTD",     CALCULATE ( [Sales], 'Time Intelligence'[Time Calculation] = "QTD" ),"YTD",     CALCULATE ( [Sales], 'Time Intelligence'[Time Calculation] = "YTD" ),"PY",      CALCULATE ( [Sales], 'Time Intelligence'[Time Calculation] = "PY" ),"PY QTD",  CALCULATE ( [Sales], 'Time Intelligence'[Time Calculation] = "PY QTD" ),"PY YTD",  CALCULATE ( [Sales], 'Time Intelligence'[Time Calculation] = "PY YTD" )),DimDate[CalendarYear] IN { 2012, 2013 }
)

9.2.3 计算组属性

计算组是一组计算项的集合，根据用户定义的标准分组在一起。它是一个简单的实体，主要由以下属性定义：

• 计算组名称：表示客户端计算组的表的名称。
• 计算组优先级：当有多个活动计算组时，定义用于将每个计算组应用于度量引用的优先级的数字。
• 计算组属性名称：这是包含计算项的列的名称，以列中可用的唯一项的形式显示给客户端。

计算项：计算项是一个复杂得多的实体，其属性列表如下：

• 计算项名称：这将成为计算组列的一个值，类似于计算组表中的一行。

• 计算项表达式：一个 DAX 表达式，可能包含一些特殊的函数，如 SELECTEDMEASURE，用于定义如何应用计算项。

• 计算项序号：由序数值决定，定义了当向用户显示不同的计算项时如何排序。
  

• 格式字符串：如果没有指定，计算项将继承其基本度量的格式字符串。但如果修饰符更改了计算，则可以使用计算项的格式覆盖度量格式字符串。

可以通过在数据窗格中双击字段、或在“属性”窗格来修改计算组、计算组列和计算项的名称。

通过右键单击计算组或“计算项”部分；或者通过计算项的属性窗格来添加计算项。

在“属性”窗格的“计算项”部分、或使用右键菜单，可以调整计算项在报表中的显示顺序 ，比如下图二者顺序是一样的：

9.2.4 动态格式字符串

  传统的FORMAT函数可以根据条件改变数值的显示格式，例如，将数字格式化为货币、百分比或带有千位分隔符的数字，但它会将度量值转换为字符串类型。这意味着原本应该是数值类型的度量值会被当作字符串处理，这可能会带来一些限制（大多数依赖于数值的 Power BI 可视化无法正确处理字符串类型的数据）。

  动态格式字符串允许对特定度量值有条件地应用格式，而不需要将度量值转换为字符串类型，此功能也适用于计算组。选中一个计算项，然后在“属性”窗格中启用“动态格式字符串”，即可使用 DAX 表达式定义格式 。例如，可以将 YOY% 计算项的格式设置为百分比 (#,##0.00%)，而不管基础度量值（如销售额或订单数）的原始格式如何。

  除了指定一个固定的格式字符串，还可以在复杂场景中使用 DAX 表达式动态生成格式字符串。假设有基础度量值：[Sales Amount]（格式设置为 "#,##0.00"），有一个货币度量值：[Selected Currency] （在之前格式上追加 “EUR”），通过以下方式实现动态格式：

-- 在计算组格式字符串定义中使用
SELECTEDMEASUREFORMATSTRING() & " " & [Selected Currency]

  定制计算项的格式字符串有助于在浏览模型时保持用户体验的一致性。当报表中有多个计算组时，格式字符串的最终效果还取决于计算组的优先级。计算组的优先级决定了计算项的执行顺序，从而影响格式字符串的应用。

9.3 理解计算组

计算项是存储在计算组表中的特殊行，其核心是通过 重写度量引用 来实现计算逻辑。

对于以下计算项：

-- Calculation Item: YTDCALCULATE (SELECTEDMEASURE (),DATESYTD ( 'Date'[Date] ))

1. 当我们使用例如切片器来应用此计算项时，等价于使用 CALCULATE 创建此筛选器：

   CALCULATE ([Sales Amount],'Time Intelligence'[Time calc] = "YTD")
   

2. DAX引擎会自动为计算项定义添加CALCULATE包装，重写表达式。原始筛选器（如YTD）传递到子表达式中，以表示此计算项已经被应用。度量值[Sales Amount]一样可以被相应的计算项替换。

   CALCULATE (CALCULATE ([Sales Amount],DATESYTD ( 'Date'[Date] )))
   

在内部 CALCULATE 中，可以使用 ISFILTERED 检查计算项是否被筛选。

9.3.1 替换度量值引用

计算项的应用实际上是用计算项表达式替换了度量值的引用，所以如果没有度量值引用，计算项不会产生任何影响。例如：

CALCULATE (SUMX ( Sales, Sales[Quantity] * Sales[Net Price] ),'Time Intelligence'[Time calc] = "YTD"
)

这等价于：

CALCULATE (SUMX ( Sales, Sales[Quantity] * Sales[Net Price] ))

9.3.2 计算项的应用范围

  计算项是以 单个度量值引用 为单位应用的，而不是整个表达式。当表达式中包含多个度量值引用时，每个引用会独立被计算项修改，这可能导致意外的行为。计算项应用的一般规则是：

1. 替换：引擎会查找表达式中的所有度量引用，将每个[Measure]替换为：

   CALCULATE([Measure], CalcItem定义)
   

2. 上下文继承：每个替换后的CALCULATE会继承外部筛选上下文，但不共享其他度量引用的修改

3. 筛选器叠加：当存在嵌套CALCULATE时，内层计算项应用可能覆盖外层筛选器。

例如， Cost Ratio YTD 度量值中的表达式包含两个度量值引用，Total Cost 和 Sales Amount ：

CR YTD :=
CALCULATE (DIVIDE ( [Total Cost], [Sales Amount] ),'Time Intelligence'[Time calc] = "YTD"
)

这等价于：

CR YTD Actual Code :=
CALCULATE (DIVIDE (CALCULATE ( [Total Cost], DATESYTD ( 'Date'[Date] ) ),CALCULATE ( [Sales Amount], DATESYTD ( 'Date'[Date] ) ))
)

在简单情况下，可改写为：

CR YTD Simplified :=
CALCULATE (CALCULATE ( DIVIDE ( [Total Cost], [Sales Amount] ), DATESYTD ( 'Date'[Date] ) )
)

然而，在更复杂的场景中，CR YTD Simplified 与CR YTD或者CR YTD Actual Code结果是不一样的。以Sales YTD 2008 2009 度量值为例：

Sales YTD 2008 2009 :=
CALCULATE (CALCULATE ([Sales Amount],'Date'[Calendar Year] = "CY 2009"  	-- 内层筛选),'Time Intelligence'[Time calc] = "YTD", -- 计算项激活'Date'[Calendar Year] = "CY 2008"      	-- 外层筛选
)

• 内部 CALCULATE：将 Sales Amount 限制在 2009 年
• 外部 CALCULATE 函数将计算项 YTD 应用于 Sales Amount，并将结果限制在 2008 年。

  但实际上，计算项虽然在外层CALCULATE被"激活"，但不会立即修改表达式，而是等待具体的度量引用出现时才应用。当内层CALCULATE明确指定CY 2009时，它会覆盖外层对同列的筛选（CY 2008），但保留计算项的激活状态。最终在计算[Sales Amount]时，使用当前有效的年份筛选（CY 2009），应用DATESYTD函数。所以以上代码等价于：

Sales YTD 2008 2009 Actual Code :=
CALCULATE (CALCULATE (CALCULATE ([Sales Amount],DATESYTD ( 'Date'[Date] )),'Date'[Calendar Year] = "CY 2009"),'Date'[Calendar Year] = "CY 2008")

• 右侧矩阵：明确展示了在CY 2009列下的YTD值

• 中间矩阵：结果与右侧的CY 2009列一致

  所以虽然外层筛选了CY 2008，但实际计算使用的是CY 2009的YTD。通过极端案例，展示了计算项如何穿透嵌套的CALCULATE，当你不经意间混合多种筛选逻辑时，可能产生的反直觉结果。所以，建议仅在调用单个度量值时使用计算项，永远不要使用计算项来更改复杂表达式的行为：

-- 最佳实践SalesPerWd :=
CALCULATE ([Sales Amount],  							 -- 单独度量值，这是好的。'Time Intelligence'[Time calc] = "YTD")-- 不好的行为SalesPerWd :=
CALCULATE (SUMX ( Customer, [Sales Amount] ),   		--复杂的表达式，它不是一个单独的'Time Intelligence'[Time calc] = "YTD"     --度量值引用)

9.3.3 复杂表达式重构

以下表达式只计算销售月份的工作日数：

SUMX (VALUES ( 'Date'[Calendar Year month] ),IF ([Sales Amount] > 0,         -- 度量值引用[# Working Days]            -- 度量值引用))

那么，每月平均销售额为：

Sales WD :=
DIVIDE ([Sales Amount],    	-- 度量值引用SUMX (VALUES ( 'Date'[Calendar Year month] ),IF ([Sales Amount] > 0,   	--度量值引用[# Working Days]   		--度量值引用)))

使用使用YTD计算项，则有：

Sales WD YTD 2008 :=
CALCULATE (DIVIDE ([Sales Amount],         		-- 度量值引用SUMX (VALUES ( 'Date'[Calendar Year month] ),IF ( [Sales Amount] > 0, 	--度量值引用[# Working Days] ) 		--度量值引用)),'Time Intelligence'[Time calc] = "YTD",'Date'[Calendar Year] = "CY 2008"
)

人们会期望前面的表达式计算每个工作日的 Sales Amount 度量值，换句话说，YTD逻辑应应用于整个比率计算：

Sales WD YTD 2008 Expected Code :=
CALCULATE (CALCULATE (DIVIDE ([Sales Amount],             -- 度量值引用SUMX (VALUES ( 'Date'[Calendar Year month] ),IF ( [Sales Amount] > 0, --度量值引用[# Working Days] )   --度量值引用)),DATESYTD ( 'Date'[Date] )),'Date'[Calendar Year] = "CY 2008"
)

实际上，计算项的应用发生在度量值引用上，而不是整个表达式上。因此，YTD逻辑分别应用于分子和分母中的每个度量值：

Sales WD YTD 2008 Actual Code :=
CALCULATE (DIVIDE (CALCULATE ([Sales Amount],DATESYTD ( 'Date'[Date] )),SUMX (VALUES ( 'Date'[Calendar Year month] ),IF (CALCULATE ([Sales Amount],DATESYTD ( 'Date'[Date] )) > 0,CALCULATE ([# Working Days],DATESYTD ( 'Date'[Date] ))))),'Date'[Calendar Year] = "CY 2008")

  正确逻辑应该是(a/b)的YTD，此公式实际执行的是(a的YTD)/(b的YTD)。当用户钻取到月份时看似正确，但汇总后，季度/年度报表完全错误。

最佳实践是"只在单个度量值上应用计算项"：

Sales WD YTD 2008 Fixed :=
CALCULATE ([Sales WD],  -- 度量值引用'Time Intelligence'[Time calc] = "YTD",'Date'[Calendar Year] = "CY 2008")

这等价于：

-- DATESYTD 提供的筛选器包围整个表达式，结果正确
Sales WD YTD 2008 Fixed Actual Code :=
CALCULATE (CALCULATE ( [Sales WD], DATESYTD ( 'Date'[Date] ) ),'Date'[Calendar Year] = "CY 2008"
)

  在使用 Power BI 等客户端工具时，用户通常不需要担心计算项（Calculation Items）的复杂细节。这些工具会自动以正确的方式应用计算项，因为它们在执行查询时总是调用单个度量值（Measure）。然而，作为 DAX 开发人员，你可能会在 CALCULATE 函数中直接使用计算项作为筛选器。在这种情况下，你需要特别注意 CALCULATE 中使用的表达式，以确保计算项被正确应用。

9.3.4 计算组优先级

   优先级是计算组的一个属性，用于确定当多个计算组应用于同一个基础度量值时，它们的计算顺序 ，数值越高，优先级越高 。

  在计算时，优先级较高的计算组的计算项首先被应用。其 SELECTEDMEASURE() 会被替换为下一个优先级较低的计算组的表达式，依此类推，直到原始的基础度量值。 动态格式字符串的优先级: 只有优先级最高的计算组的动态格式字符串会被应用到最终结果 ，基础度量值自身的动态格式字符串优先级最低 。

  如果有一个优先级为 100 的计算组执行“加 2”操作，另一个优先级为 200 的计算组执行“乘以 2”的操作，那么对于一个值为 10 的基础度量值，最终的计算将是 ((10) + 2) * 2 = 24 。

'Measure group'[Measure] = 10     												//基础度量值
'Calc Group 1 (Precedence 100)'[Calc item (Plus 2)] = SELECTEDMEASURE() + 2   	//计算组1，优先级100
'Calc Group 2 (Precedence 200)'[Calc item (Times 2)] = SELECTEDMEASURE() * 2   	//计算组2，优先级200
( SELECTEDMEASURE() + 2 ) * 2													//选择两个切片器之后，合并 DAX 表达式

假设我们有两个计算组：

• 时间智能计算组（Time Intelligence）：包含计算项 YTD，用于计算年至今的值。

  -- Calculation Group: 'Time Intelligence'[Time calc]
  -- Calculation Item: YTD
  CALCULATE (SELECTEDMEASURE (),DATESYTD ( 'Date'[Date] )
  )
  

• 平均值计算组（Averages）：包含计算项 Daily AVG，用于计算日平均值。

  -- Calculation Group: 'Averages'[Averages]
  -- Calculation Item: Daily AVG
  DIVIDE (SELECTEDMEASURE (),COUNTROWS ( 'Date' )
  )
  

使用两个度量值分别调用这两个计算项，工作的很好：

YTD := CALCULATE ( [Sales Amount], 'Time Aggregation'[Aggregation] = "YTD" )
Daily AVG := CALCULATE ( [Sales Amount], 'Averages'[Averages] = "Daily AVG" )

现在，我们定义一个度量值 Daily YTD AVG，同时应用这两个计算项，用于计算年初至今的平均销售额：

Daily YTD AVG :=
CALCULATE ([Sales Amount],'Time Intelligence'[Time calc] = "YTD",'Averages'[Averages] = "Daily AVG"
)

  正常的逻辑是先将计算上下文更新到年初至今的区间中，再进行均值计算，即Time Intelligence 计算组的优先级应该更高。计算展开为：

CALCULATE (DIVIDE ([Sales Amount],COUNTROWS ( 'Date' )),DATESYTD ( 'Date'[Date] ))

此时得到了正确的结果：

反之，如果Daily AVG 计算组优先级更高，那么Daily AVG首先被应用：

DIVIDE (CALCULATE ([Sales Amount],DATESYTD ( 'Date'[Date] )),COUNTROWS ( 'Date' ))

计算结果是今年年初至今销售额/本月天数，平均销售额越来越大：

9.4 横向递归

9.4.1 横向递归简介

   在同一计算组中，计算项可以引用组内其它的计算项，这种现象称为横向递归。仅当这些引用位于不同的 CALCULATE 语句中时才被支持，因为每个 CALCULATE 会创建不同的筛选器上下文并独立评估表达式。另外，相互引用的计算项不能形成循环依赖，以免导致无限循环。

  横向递归在处理时间智能计算时尤为有用。例如，在时间智能计算组中定义了 YTD（Year to Date，年初至今）和 SPLY（Same Period Last Year，去年同期）。如果需要计算 PYTD（Previous Year to Date，前一年年初至今），可以通过以下两种方式实现：

1. 直接使用时间智能函数：直接使用时间智能函数，简单且高效。

   PYTD := CALCULATE (SELECTEDMEASURE (),DATESYTD ( SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
   )
   

2. 引用已有的计算项：虽然代码稍显复杂，但可以避免重复编写相同的逻辑。

   PYTD := CALCULATE (SELECTEDMEASURE (),SAMEPERIODLASTYEAR ( 'Date'[Date] ),'Time Intelligence'[Time calc] = "YTD"
   )
   

9.4.2 横向递归的限制

尽管横向递归提供了一定程度的灵活性，但它也带来了复杂性和潜在的风险。例如，如果计算项之间存在循环依赖，DAX 引擎将无法正确处理，导致错误。以下是一个典型的循环依赖示例：

-------------------------------------------------------
-- Calculation Group: Infinite[Loop]
-------------------------------------------------------
--
-- Calculation Item: Loop A
--
CALCULATE (SELECTEDMEASURE (),Infinite[Loop] = "Loop B"
)
--
-- Calculation Item: Loop B
--
CALCULATE (SELECTEDMEASURE (),Infinite[Loop] = "Loop A"
)

在这个例子中，计算项 A 依赖于计算项 B，而计算项 B 又依赖于计算项 A，从而形成了一个无限循环。DAX 引擎能够检测到这种循环依赖，并抛出错误。

9.4.3 横向递归的复杂性

横向递归可能导致非常复杂的表达式，这些表达式不仅难以阅读，还可能产生意想不到的结果。例如，当度量值在计算项之间设置筛选器时，可能会引发复杂的递归调用。

--
-- Measures definition
--
Sales Amount := SUMX ( Sales, Sales[Quantity] * Sales[Net Price] )
MA := CALCULATE ( [Sales Amount], Infinite[Loop] = "A" )
MB := CALCULATE ( [Sales Amount], Infinite[Loop] = "B" )
-------------------------------------------------------
-- Calculation Group: Infinite[Loop]
-------------------------------------------------------
--
-- Calculation Item: A
--
[MB]
--
-- Calculation Item: B
--
[MA]

在这个例子中，计算项 A 和 B 本身并不直接引用对方，但它们通过引用度量值 MA 和 MB 间接形成了循环依赖。DAX 引擎能够检测到这种复杂的循环依赖，并抛出错误。为了避免横向递归带来的复杂性和潜在错误，建议：

1. 限制横向递归的使用：尽量避免在代码中频繁使用横向递归。虽然这可能意味着在多个位置重复相同的代码，但可以减少复杂性和错误的风险。
2. 隐藏计算组：如果需要使用横向递归，建议将计算组隐藏，使其只能通过代码管理，而不是由用户通过用户界面（如 Power BI 中的切片器）直接操作。这样可以避免用户在不了解递归原理的情况下生成错误。
3. 逐步调试与验证：在使用横向递归时，建议逐步调试和验证每个计算项的逻辑，确保没有循环依赖。可以通过逐步添加计算项并观察结果来验证逻辑的正确性。

隐藏计算组，就是不要将计算组应用在切片器、筛选器这些报表界面中，以免用户误操作导致循环引用。

9.5 选择表达式 (预览功能)

选择表达式是为计算组定义的可选属性。目前，共有两种类型的选择表达式：

• multipleOrEmptySelectionExpression：未定义时默认不进行筛选 ， 你也可以通过此表达式返回自定义文本或执行特定计算。以下三种情况触发：
  • 选择了多个计算项；
  • 选择了不存在的计算项；
  • 进行了冲突的选择。
• noSelectionExpression：当计算组未被筛选时触发，可用于实现默认行为。例如在没有选择任何货币时自动转换为基准货币 。

这两个选择表达式还可以关联一个 formatStringDefinition 来定义动态格式字符串 ，例如：

...
"calculationGroup": {"multipleOrEmptySelectionExpression": {"expression": "","formatStringDefinition": {...}},"noSelectionExpression": {"expression": "","formatStringDefinition": {...}}
...
}

这些表达式（如果指定）仅适用于上述特定情况。 单个计算项的选择不受这些表达式的影响。

9.5.1 未定义选择表达式

下表概述了在未定义选择表达式时不同选择类型的默认行为：

选择类型	未定义选择表达式（默认）
单个选择	应用所选内容
多个选择	计算组不被筛选
空选择	计算组不被筛选
没有选择	计算组不被筛选

9.5.2 已定义选择表达式

  如果对同一计算组进行了多个选择，则计算组将计算并返回 multipleOrEmptySelectionExpression定义的结果。 如果尚未定义此表达式，则计算组将返回SELECTEDMEASURE()。

  假设有一个名为 “MyCalcGroup” 的计算组，并配置了以下 multipleOrEmptySelectionExpression：

IF (
ISFILTERED ( 'MyCalcGroup' ),"Filters: " & CONCATENATEX ( FILTERS ( 'MyCalcGroup'[Name] ),'MyCalcGroup'[Name], ", ")
)

1. 多项选择：使用以下查询，选择了 “item1” 和 “item2” 这两个计算项，将返回**“Filters: item1, item2”**。

   EVALUATE
   {CALCULATE ([MyMeasure],'MyCalcGroup'[Name] = "item1" || 'MyCalcGroup'[Name] = "item2")
   }
   

2. 空选择：执行以下查询，选择了不存在的 “item4”，将返回："Filters: "。

   EVALUATE
   {CALCULATE ([MyMeasure],'MyCalcGroup'[Name] = "item4" -- item4 does not exists)
   }
   

3. 当计算组没有被筛选时，将应用 noSelectionExpression。它主要用于在用户未采取任何操作的情况下执行默认操作，同时仍然为用户提供了覆盖默认操作的灵活性。
   例如，以美元作为中心枢轴货币的自动货币转换，可以设置一个具有以下 noSelectionExpression 的计算组：

   IF ( //检查上下文中是否只有一种货币且不是作为枢轴货币的美元:SELECTEDVALUE ( DimCurrency[CurrencyName], "US Dollar" ) = "US Dollar",SELECTEDMEASURE (),SUMX (VALUES ( DimDate[DateKey] ),CALCULATE (DIVIDE (SELECTEDMEASURE (),MAX ( FactCurrencyRate[EndOfDayRate] )))))
   

   同时为此表达式设置 formatStringDefinition：

   SELECTEDVALUE( DimCurrency[FormatString], SELECTEDMEASUREFORMATSTRING() )
   

   现在，如果没有选择任何货币，所有货币将根据需要自动转换为枢轴货币（美元）。此外，用户仍然可以选择其他货币进行转换，而无需像没有 noSelectionExpression 那样切换计算项。

  总而言之，选择表达式为处理计算组在特定选择场景下的行为提供了额外的控制和灵活性。multipleOrEmptySelectionExpression 用于管理多选、空选或无效选择的情况，而 noSelectionExpression 则用于定义在计算组未被筛选时的默认行为，例如在货币转换场景中自动应用枢轴货币。