15、常用迭代函数详解

  本篇文章将详细介绍常用迭代函数的计值流程与各种细节。迭代函数在DAX中是很重要的一类函数，这类函数就类似于编程语言里的循环体，在计算中常常充当发动机的角色，其重要性不言而喻。此外，筛选器的其中一种重要来源就是由行上下文转换所得到，而行上下文大部分都是由迭代函数所提供，因此迭代函数是我们必须要掌握的一类函数。

  DAX里的迭代函数的迭代与广义上的迭代的含义有所不同，广义上的迭代指的是重复反馈过程的活动，其目的通常是为了逼近所需目标或结果，将每一次对过程的重复称为一次“迭代”，而每一次迭代得到的结果会作为下一次迭代的初始值。而在DAX里的迭代可以理解为遍历，迭代函数会遍历其参数所指定表的每一行，并为表的每一行执行相同的 DAX 表达式，在遍历完表后再根据不同的迭代函数执行不同的后续操作，例如：SUMX的后续操作是求和、MAXX的后续操作是求最大值、RANKX的后续操作是排序等等。

  DAX里的迭代函数有很多，每一个迭代函数都有其各自的作用，但大致可以分为以下两类：

1. 以X结尾的迭代函数，如：SUMX、MAXX、MINX、AVERAGEX、COUNTX、COUNTAX、PRODUCTX 等等，这些函数的主要特点可以概况为：结果为标量数值，且以X结尾。
2. 另一类迭代函数要么结果不是标量数值，要么就是行为比较特殊，如：FILTER、ADDCOLUMNS、SELECTCOLUMNS、RANKX、TOPN、FIRSTNONBLANK 等等。

  由于迭代函数过多，而且不同的函数作用不同且需要注意的地方也不同，在一篇文章里不可能全部介绍，所以本篇文章只介绍第一类的迭代函数以及FILTER，其余的迭代函数将在后续文章中介绍。

  虽说DAX的迭代函数有许多，但是这些迭代函数的行为方式与语法结构都是差不多的，仅仅是在遍历完表后的后续操作有所不同。因此，迭代函数的行为可以总结为如下：

• Step1. 在外部计值环境下计算其参数所指定的表（一般为第一参数），以确定迭代目标；
• Step2. 为指定的表的每一行创建行上下文；
• Step3. 从指定的表的第一行开始，在外部计值环境以及迭代函数内部所创建的行上下文中计算其余参数（一般为第二参数），并依次迭代指定表中的所有行；
• Step4. 对上一步计算出的其余参数的结果执行后续操作，一般为聚合操作，例如：SUMX 求和、MINX 取最小值、AVERAGEX 取平均值等等，以此类推。具体如下图所示：

  其中，行上下文是DAX引擎用来区分各列在不同行的值的一个标记集合，迭代函数在迭代到某一行时，就会根据这个标记集合来获取各列在当前行的数据，因此，我们可以把行上下文简单理解成正在计算的当前行，关于行上下文的更多内容将在后续文章中介绍。

  对于上文提到的第一类迭代函数来说，它们只有两个参数，第一个参数是用来确定迭代目标的表函数，第二参数是任意DAX表达式，主要是为第一参数的每一行数据计值。其中，第一参数所处的计值环境是迭代函数所处的外部计值环境，第二参数所处的计值环境既包括了迭代函数所处的外部计值环境，也包括了迭代函数内部对第一参数的表所创建的行上下文。要知道，一个DAX表达式，甚至是一个DAX函数，若它所处的计值环境不同，那么它所得到的结果也有可能会不同。因此，我们一定要记住，迭代函数的第一参数与第二参数所处的计值环境是不同的。

  虽然各个迭代函数的作用不同，但是它们的行为模式和语法结构都是一致的，经过上面的介绍，相信对迭代函数的行为都有了一定的了解，因此在这里我不会详细地演示每一个迭代函数的行为，只介绍它们的语法结构和注意事项。

  在DAX中，常用的数据类型一般为：数值、文本、逻辑值、日期以及空值，而不同的迭代函数在聚合的过程中所支持的数据类型也是有所不同的。由于聚合结果为标量数值的迭代函数一般都不会对逻辑值和日期进行聚合，并且都忽略空值，因此为了方便记忆，我将常用的迭代函数分为了两类，一类是只支持数值，另一类则是同时支持数值和文本。此外，对于逻辑值，许多迭代函数都是不支持的，但当你遇到以A结尾的聚合函数或倒数第二个字母为A的迭代函数时就需要注意了，因为这些聚合函数或者迭代函数一般都支持逻辑值，例如：COUNTAX、MAXA、MINA、AVERAGEA等等，一般以聚合函数居多。

  只支持数值的迭代函数

• SUMX

SUMX ( <Table>, <Expression> )

  迭代<Table>的每一行并对<Expression>计值，最后将<Expression>所得到的所有结果进行求和。SUMX只考虑列中的数值，忽略空值，不支持文本、逻辑值。

  注意事项：SUMX也可以对日期进行聚合，但实际上是对日期所代表的数值进行累加，因此并无实际意义。

• AVERAGEX

AVERAGEX(<Table>,<Expression>)

  迭代<Table>的每一行并对<Expression>计值，最后对<Expression>所得到的所有结果计算算术平均值。AVERAGEX只考虑列中的数值，忽略空值，不支持文本、逻辑值。

  注意事项：AVERAGEX忽略空值将改变分母的值，例如：有10个值的列中，空值占了3个，那么在求平均的时候，分母应该为7，而不是10。此外，AVERAGEX也可以对日期进行聚合，但实际上是对日期所代表的数值求平均，因此并无实际意义。

• PRODUCTX

PRODUCTX(<Table>, <Expression>)

  迭代<Table>的每一行并对<Expression>计值，最后将<Expression>所得到的所有结果相乘。PRODUCTX只考虑列中的数值，忽略空值，不支持文本、逻辑值。

  同时支持数值和文本的迭代函数

• MAXX

MAXX(<Table>,<Expression>)

  迭代<Table>的每一行并对<Expression>计值，最后从<Expression>所得到的所有结果里取最大值。MAXX支持数值、文本、日期，忽略空值，不支持逻辑值。

  注意事项：MAX与MAXX支持文本，是按照字符顺序来取最大值的。而MAXA也支持文本，但却是将文本视为0。虽然都是支持文本，但支持的方式略有不同，需要注意。

• MINX

MINX(<Table>,<Expression>)

  迭代<Table>的每一行并对<Expression>计值，最后从<Expression>所得到的所有结果里取最小值。MINX支持数值、文本、日期，忽略空值，不支持逻辑值。

  注意事项：MIN与MINX支持文本，是按照字符顺序来取最小值的。而MINA也支持文本，但却是将文本视为0。虽然都是支持文本，但支持的方式略有不同，需要注意。

• COUNTX

COUNTX(<Table>,<Expression>)

  迭代<Table>的每一行并对<Expression>计值，最后从<Expression>所得到的所有结果里计算非空值的行数。COUNTX支持数值、文本、日期，忽略空值，不支持逻辑值。

• COUNTAX

COUNTAX(<Table>,<Expression>)

  迭代<Table>的每一行并对<Expression>计值，最后从<Expression>所得到的所有结果里计算非空值的行数。COUNTAX支持数值、文本、日期，忽略空值，支持逻辑值。

  从上面的介绍中我们可以知道，常用的迭代函数通常是以X结尾的，而去除X后俨然是一个聚合函数，那么这些聚合函数与迭代函数之间有什么关系呢？其实聚合函数可以当作一种语法糖，是迭代函数的简化形式，但是聚合函数与迭代函数的性能方面都是一样的。下面就给出几个聚合函数的等价写法，具体如下：

1、SUM

SUM('Table'[ColumnName])

---- 等价于 ----

SUMX('Table','Table'[ColumnName])

2、AVERAGE

AVERAGE('Table'[ColumnName])

---- 等价于 ----

AVERAGEX('Table','Table'[ColumnName])

3、MAX

MAX('Table'[ColumnName])

---- 等价于 ----

MAXX('Table','Table'[ColumnName])

  其它的聚合函数的等价写法都是类似的，因此就不再过多介绍。当我们只需要对基础表中的单独一列进行聚合时，直接使用聚合函数一种不错的方式，能减少代码量的书写。但当我们的计算涉及多列，或者计算复杂度很高，那么就要使用迭代函数的完整写法了。

  下面我们来重点看一下FILTER函数，在讲解之前，先给出FILTER函数的语法结构和函数作用：

• FILTER

FILTER ( <Table>, <FilterExpression> )

  迭代<Table>的每一行并对<FilterExpression>计值，当计值结果为 TRUE，保留当前行；否则，就删除当前行。最终返回一个表，表中的每一行都是<FilterExpression>为TRUE时保留下来的行。其中，<FilterExpression>必须为逻辑值，否则将尝试自动转换成逻辑值，当转换失败时报错。

  FILTER函数是一个返回表的表函数，它是DAX的核心函数之一，它的函数作用就和它的函数名称一样，就是筛选，它会筛选出第二参数的表达式为真时的行。此外，FILTER函数虽然不以X结尾，但它也是一个迭代函数，所以它的计值流程与上面介绍的是一样的，就不再过多介绍，下面介绍一下FILTER的性能优化。

  由于FILTER函数能够返回一张表，因此，我们可以将FILTER函数继续嵌套在另一个FILTER函数的第一参数里面。那么嵌套FILTER函数有什么好处呢？好处就是能够对包含多个关系为且的筛选条件的FILTER表达式进行性能调优。

  首先，嵌套FILTER的表达式的结果与在一个FILTER里使用AND或&&将条件连接起来所得到的结果是一样的。例如，下面两个表达式的结果就是一样的：

FILTER ( <Table>, AND ( <condition1>, < condition2> ) )

FILTER ( FILTER ( <Table>, < condition1> ), < condition2> )

  但是，虽然结果是相同的，可是上面两个表达式的执行顺序却是不同的，第一个FILTER的两个条件是同时进行判断的，而第二个FILTER是先执行内层的FILTER，所以是先判断<condition1>再判断<condition2> 。可千万别小看这个执行顺序的改变，虽然结果是一样的，但是改变执行顺序的同时也会改变表达式的性能，当数据量非常大的时候我们就可以利用这一点来优化表达式的性能。当然，由于DAX引擎的性能已经非常优越了，所以在数据量小的时候是体现不出这种差别的。

  通过利用嵌套FILTER的执行顺序不同这一特点，可以将约束性最强或计算速度最快的筛选条件放到内层FILTER里先执行。因为约束性越强就意味着筛选后所得到的表的行数越少，这样可以明显减轻外层FILTER的迭代数量，从而提升性能。如果只使用一个FILTER来判断多个筛选条件，那么所有的筛选条件都会迭代表的所有行，假设每个筛选条件消耗的计算资源都一致，那么就是两份消耗。但若将约束性强的筛选条件放到内层FILTER里，那么内层FILTER仍然是迭代表的所有行，消耗一份计算资源，但外层FILTER却能大大减少迭代的行数，使得计算资源的消耗减少，最终整个嵌套的FILTER表达式可能就只消耗一点五份计算资源。

  此外，还可以将计算速度最快的筛选条件放到内层FILTER里先执行，因为计算速度快就意味着判断的速度快！若筛选条件间的计算速度差异较大，却还是只用一个FILTER，那么这些筛选条件都将同时判断，虽然计算速度快的筛选条件判断完了，但还是需要等待其他筛选条件进行判断，这性能自然就较差了。

  最后，下面有三个结果相同的表达式，你们可以判断一下哪个表达式的执行时间最快，结果就不告诉你们了，感兴趣的可以自行模拟数据，并使用DAX Studio去测试，DAX表达式如下：

CALCULATE(
    COUNTROWS('销售表'),
    FILTER(ALL('销售表'),
        '销售表'[销售日期]>=DATE(2015,1,1)
            && '销售表'[销售日期]<=DATE(2016,12,31)
    )
)

CALCULATE(
    COUNTROWS('销售表'),
    FILTER(
        FILTER(ALL('销售表'),'销售表'[销售日期]>=DATE(2015,1,1)),
        '销售表'[销售表日期]<=DATE(2016,12,31)
    )
)

CALCULATE(
    COUNTROWS('销售表'),
    FILTER(ALL('销售表'),'销售表'[销售日期]>=DATE(2015,1,1)),
    FILTER(ALL('销售表'), '销售表'[销售日期]<=DATE(2016,12,31))
)

  DAX系列文章中涉及到的案例文件，均已上传到QQ群：344353627，若有需要，可自行加群获取。