以X结尾的迭代函数

在 Excel 中，我们习惯使用聚合函数一步一步地执行计算。比如为了计算总销售额，你创建了一列，用价格乘以数量，第二步，你将这一列求和以计算总销售额。这个数字会很有用，例如作为计算每个产品销售额占比的分母。


使用 DAX 的迭代函数，我们只需要一步就可以完成相同的操作。迭代函数完全按照它的名字表示的意思来执行：迭代整张表，并对表的每一行执行计算，最后聚合结果以生成所需的单个值，这类迭代函数使用的是聚合函数+X 的形式。






初识迭代函数

如果需要计算所有销售额的总和，有的用户可能会尝试写出这样的公式

1. [AllSales]：= SUM(Sales[ProductQuantity] * Sales[ProductPrice])

复制代码

实际上，这是不被允许的，因为聚合函数只能使用单个列作为参数。你可以使用迭代函数 SUMX 计算所有销售额的总和，它逐行计算 Sales 表的销售额，将所有结果相加得到最终答案:

1. [AllSales] :=
   
2. SUMX (
   
3.     Sales,
   
4.     Sales[ProductQuantity] * Sales[ProductPrice]
   
5. )

复制代码

这种方法既有优点也有缺点。好处是，你可以在单个步骤中执行许多复杂的计算，而不必为此添加许多列，这对某些特定的公式很有用。另一方面，由于使用 DAX 编程的视觉直观性不如 Excel。实际上，你观察不到那个将价格和数量相乘的虚拟列；它只短暂的存在于公式运行的时候。


你仍然可以选择创建一个计算列来计算价格和数量的乘积。然而，在某些情况下这不是一个好的做法，因为它占用了更多的内存，并可能会减慢模型刷新速度。

语法示例

聚合函数语法

1. Aggregate ( <ColumnName> )

复制代码

迭代函数语法

1. AggreateX ( <Table>, <Expression> )

复制代码

将某些聚合函数结尾加 X 可以构成迭代函数，迭代函数语法结构基本相同，都以相同方式工作，仅最后的聚合操作有所不同。具体如下：

• 在现有上下文中计算第一个参数，以确定要扫描的行。
  
• 为上一步计算的表的每一行创建一个新的行上下文。
  
• 迭代表并在现有计值上下文中计值第二个参数，现有上下文中包括新创建的行上下文。
  
• 聚合前一步中计算的值（SUMX 求和、MINX 取最小值、AVERAGEX 取平均值 以此类推）
  

常用的迭代函数
SUMX

对<Table>的每一行计算表达式<Expression>，将得到的所有结果求和。SUMX只考虑列中的数字，空白、逻辑值和文本将被忽略。

1. SUMX ( <Table>, <Expression> )

复制代码

AVERAGEX

对<Table>的每一行计算表达式<Expression>，将得到的所有结果计算算术平均值。AVERAGEX 忽略空值。

1. AVERAGEX(<table>,<expression>)

复制代码

MINX

对<Table>的每一行计算表达式<Expression>，从得到的所有结果中取最小值。MINX 支持数值，文本和日期类型，忽略空值，不支持逻辑值 TRUE/FALSE。

1. MINX(<table>, < expression>)

复制代码

MAXX

对<Table>的每一行计算表达式<Expression>，从得到的所有结果中取最大值。MAXX 支持数值，文本和日期类型，忽略空值，不支持逻辑值 TRUE/FALSE。

1. MAXX(<table>,<expression>)

复制代码

COUNTX

对<Table>的每一行计算表达式<Expression>，计算包含非空白值或计算结果为非空白值的表达式的行数。COUNTX 支持数值，文本和日期类型，不支持逻辑值，如果没有要计数的行则返回空值。

1. COUNTX(<table>,<expression>)

复制代码

COUNTAX

对<Table>的每一行计算表达式<Expression>，计算包含非空白值或计算结果为非空白值的表达式的行数。COUNTX 支持数值，文本和日期类型，支持逻辑值，如果没有要计数的行则返回空值。

1. COUNTAX ( <Table>, <Expression> )

复制代码

PRODUCTX


对<Table>的每一行计算表达式<Expression>，将得到的所有结果相乘。PRODUCTX 仅支持数值，空白、逻辑值和文本将被忽略。

1. PRODUCTX(<table>, <expression>)

复制代码

本文以 SUMX 为例介绍这类迭代函数。

另有两个常用的 X 结尾的函数，RANKX 和 CONCATENATEX 将在统计类函数中介绍

与返回表的表达式的配合使用

除了直接使用表作为迭代函数的第一参数，你也可以使用返回表的表达式作为参数, 为表的每行计算 DAX 表达式。例如

1. [Sales Amount] :=
   
2. SUMX (
   
3.     Sales,
   
4.     Sales[Quantity] * Sales[Unit Price]
   
5. )
   

复制代码

你可以使用其他表函数替换对销售表引用。例如, 你可以使用 FILTER 函数筛选数量大于 1 的销售记录：

1. [Sales Amount Multiple Items] :=
   
2. SUMX (
   
3.     FILTER (
   
4.         Sales,
   
5.         Sales[Quantity] > 1
   
6.     ),
   
7.     Sales[Quantity] * Sales[Unit Price]
   
8. )
   

复制代码

在计算列中, 还可以使用 RELATEDTABLE 函数检索位于一对多关系多端的表的所有行。例如，以下产品表的计算列统计所有产品对应的销售金额:

1. Product[Product Sales Amount] =
   
2. SUMX (
   
3.     RELATEDTABLE ( Sales ),
   
4.     Sales[Quantity] * Sales[Unit Price]
   
5. )
   

复制代码

在基础函数类型 &#8211; 关系函数 一文中，可以找到 RELATEDTABLE 函数的详细说明。


你可以将对表函数的调用嵌套在同一个 DAX 表达式中，因为任何表表达式都可以是对表函数的调用。例如，在下面的产品表计算列中，只考虑数量大于 1 的销售记录，计算产品销售额。

1. Product[Product Sales Amount Multiple Items] =
   
2. SUMX (
   
3.     FILTER (
   
4.         RELATEDTABLE ( Sales ),
   
5.         Sales[Quantity] > 1
   
6.     ),
   
7.     Sales[Quantity] * Sales[Unit Price]
   
8. )
   

复制代码

当发生表函数的嵌套调用时, DAX 首先计算最内层函数, 然后逐级计算到最外层。不要将此规则与函数调用参数时的计算顺序混淆。

控制计算发生的详细级别

聚合函数的计算默认发生在筛选上下文一致。以 X 结尾的迭代函数因为引入了表作为参数，可以通过对表的汇总级别的控制，调整计算发生的级别。


例如你可以按销售大区汇总产品销售金额，如果使用 SUM 函数，销售额将在大区级别进行汇总。如果使用 SUMX 函数，稍微修改第一参数，可以将计算级别下移至更细的城市颗粒度

1. = SUMX ( VALUES ( 'Table'[City] ), CALCULATE ( SUM ( 'Table'[Sales] ) ) )

复制代码

目前这个公式的结果与原计算一致，而一旦你需要在大区级别显示各城市销售额总和的平均值 ，又不希望将城市显示到报告上，这种写法会非常有用。Tableau 详细级别表达式中的行上下文，这对于理解 DAX 计值流非常重要，在理解行上下文中对这部分知识做过详细介绍。

聚合函数的本质

聚合函数 SUM 与迭代函数 SUMX 看起来是两个不同的函数，实际上它们之间存在某种相似性，与之类似的 MIN 和 MINX、MAX 和 MAXX 等组合也具备这种相似性，理解这种相似性有助于你理解为什么聚合函数忽略行上下文，只考虑筛选上下文。也会加深你对 DAX 的理解。

1. SUM('Table'[Sales])
   
2. 
   
3. ---- 等价于 ----
   
4. 
   
5. SUMX('Table','Table'[Sales])

复制代码

在等价形式中，SUMX 第一参数在外部筛选上下文环境中计值，忽略行上下文，这也解释了为什么聚合函数具备相同的行为。
迭代函数的性能问题

因为 SUMX 是一个迭代器，您可能认为 SUMX 本质上是低效的。但事实并不是这么简单。因为 DAX 引擎已经被优化到可以高效地处理迭代器。不过话虽如此，糟糕的 DAX 肯定会导致 SUMX 效率低下，原因是这样的：


DAX 有 2 个计算引擎，即存储引擎(SE)和公式引擎(FE)。SE 更快，支持多线程和缓存。FE 较慢，单线程且不缓存。DAX 优化是一个复杂的主题，这里只介绍一点点相关内容。当了解这些信息后，意味着你应该编写公式来尽可能地利用 SE。当然，如果你不知道如何做到这一点，这可能会很难，下面的提示也许可以帮助你。

• sum()总是使用 SE 进行计算，不需要担心优化问题
  
• 对于大多数简单的计算(如 Sales[qty]*sales[price per unit])，SUMX()也将使用 SE，因此这些计算都没有优化问题。
  
• 在某些情况下，SUMX()可能会使用 FE 来执行部分或全部计算，特别是当您的公式中有一个复杂的比较语句时。如果 SUMX 需要使用 FE，那么性能可能会很慢，有时甚至非常慢。
  

关于第 3 点，最后避免在 SUMX 函数中编写复杂的条件语句，例如 IF 判断。考虑以下两个公式：

1. Total Sales of Items more than $100 Bad
   
2.       = SUMX(Sales,
   
3.           IF(Sales[ExtendedAmount] > 100, Sales[ExtendedAmount])
   
4.          )

复制代码

1. Total Sales of Items more than $100 Good
   
2.       = CALCULATE(
   
3.            SUMX(Sales,Sales[ExtendedAmount]),
   
4.            Sales[ExtendedAmount] > 100
   
5.         )

复制代码

低性能公式在 SUMX 中有一个 IF 语句。此 if 语句强制存储引擎将计算任务传递给公式引擎进行比较检查，以确定每个单独的行是否大于 100，然后再决定是否将其包括在计算中。因此，公式引擎必须一次一行地完成任务，从而使计算变得缓慢且效率低下。


第二个高性能公式中首先使用 CALCULATE 转换来自视觉级别的初始筛选器，以在 Sales[ExtendedAmount]>100 上添加额外的筛选器。存储引擎非常有效地应用了这个新筛选器。在 CALCULATE 修改筛选器之后，SUMX()可以使用存储引擎而不是公式引擎应用的新筛选器集来完成将剩余行相加的工作。因此，第二个公式性能更好。在一些简单测试中，第一个公式比第二个公式花费的时间长 5 倍。在其他情况下，它可能会慢 100 倍甚至 1000 倍，所以这种写法可能带来很大的问题。

众里寻他千百度，蓦然回首在这里！

太棒了，感谢唐楼主精彩的分享

努力,努力,再努力!!!!!!!!!!!

确实不错，顶先

呵呵,明白了

这个不错，学习了

越办越好，经常过来学习视频~~~~~~~~~

有空一起交流一下

边撸边过