跳至正文

迭代计算（中）：DAX迭代计算的分类

标签:

by 喜乐君
2024-07-12 update
2026/2/25 update

在学习Power BI的DAX过程中，会发现很多常见于编程语言的概念，比如迭代iteration、赋值（var）、定义DEFINE等，这也是它不同于Tableau、SQL的关键特征——SQL中没有赋值和循环的处理，Tableau计算虽有参数但无显性的循环方法，Power BI既有参数（以数据表的形式出现），也有多种间接实现循环的方法（比如EALIER函数，虽然并不容易，但至少可操作）。

“SQL中没有赋值或者循环的处理，数据也不以记录为单位进行处理，而以集合为单位进行处理。SQL和关系数据库的思维方式更像是一种整体论的思维方式。”——SQL进阶教程

要完全理解DAX语法，理解DAX相对于SQL/Tableau在计算上的关键差异，就先要理解一些关键概念：迭代iterate、循环loop、遍历（traversal）、递归（recursion）等。它们既有差异，又相互联系，是计算背后接近算法的部分，是辅助理解复杂问题解决方案的关键概念。

本文的重点是迭代（iteration），这也是DAX“计算列”的关键。

先从最简单的计数来理解迭代（iteration）的过程，之后介绍迭代的分类及其组合形式，这是理解各种迭代器（iterator）函数的基础，比如SUM、SUMX和FILTER。

  • 迭代函数必然包含两个参数：数据表、表达式——即扫描哪个数据表、执行什么计算
  • DAX中，SUMX和FILTER是典型的迭代函数，前者聚合数据，后者过滤数据

一、迭代iteration过程及其分类

何为迭代（iteration）？迭代既是程序设计中的关键概念，指程序设计中的重复计算，也被引用到社会科学领域，比如说管理理念或课程体系“迭代升级”。理解迭代的关键是“重复”，这种重复是前后依赖的重复，就像人类的文明进化总是站在前人的文明成就基础上；同时，“重复”意味着计算逻辑内在一致，不能多种方式混淆。

Iteration： repetition of a mathematical or computational procedure

DAX作为近似编程语言的分析工具、函数式分析语言，也引入了程序设计的迭代概念。迭代（iteration）指按照指定顺序、逐个访问数据集中的每一项。

理解迭代的关键有两点：第一，迭代是“逐个访问“（one by one），也可以理解为重复（repetition）；其二，迭代是整个表，不能是表的子集。

这里先以简单迭代（计数）辅助帮助理解迭代iteration的过程；而后介绍两类“迭代器”（iterator）和函数。

1、计数：自上而下、输出唯一值的业务分析

迭代其实就在我们身边，体育课出勤计数、老师收作业、定投基金，这些场景中都有迭代的影子。

举个形象的例子，体育课时很多人人站为一个队列，老师想要知道有多少人来上课了，就会让大家从头到尾、依次报数。报数自动止于最后一人，最后一人的报数，既是他在队列中的标记，也是队列的总人数。分析中，把这个过程称之为计数（COUNT）。

计数，可以说是最简单的迭代。计数迭代的关键是访问整个队列，队列中的元素依次+1。

我们既可以可视化地把多个人想象成为一个队列，也可以用数学的方法记作一个数据集。不管外在的展现形式如何，想要知道有多少人（多少个元素），必须依次、逐个标记数字，这个过程就是迭代数据集。

添加图片注释，不超过 140 字（可选）

对数据库中特定数据表的计数也是同理。区别在于，常见数据表是多行多列构成的关系结构（relational schema)。数据表的每一行是一个元组（tuple），元组中可以包含不同类型的多个数据值，比如（王五，10，0.4），换个方式理解，元组就是行（row）、记录（record）。多个行构成了数据表。

对数据表的计数计算，就是计算有多少个元组。参考之前的逻辑，相当于逐行扫描数据表中的每一行（scan table row by row），然后标记1、2、3……的过程，扫描到最后一行对应的数字，就是数据表的行数。

数据表中的计数

产品经理、产品工程师把计数计算整合、封装为COUNTX函数，分析师就可以直接引用了。

比如计算客户表中客户数量：

COUNT ( Customer[Customer Name] )
COUNTX ( Customer, Customer[Customer Name] )
COUNTROWS('table name')

[勘误]由于COUNTROWS是在视图中的聚合函数，而非迭代函数，因此更正为COUNTX。

在分析中，还有很多与之类似的迭代函数，典型的有SUMX求和、AVERAGEX算术平均、MAXX最大值。站在迭代的角度看，这些函数就是生成迭代的“迭代器”（iterators）；而从明细到抽象的计算角度看，这些函数都是由多变少的“聚合函数“（aggregators）。

迭代函数中的表达式可以是多个字段构成的计算，比如先逐行计算每一行的数量*单价，再跨行聚合。

Sales[Sales Amount]=        SUMX(Sales,Sales[Quantity]*Sales[Net Price])

从分析的角度看，可聚合的迭代函数，又称之为分析函数。

2.日期计算：自左到右、输出数据表的数据准备

迭代的核心是逐一、全量的计算，分析中有很多这样的函数，尤其是在数据准备阶段。比如日期函数、字符串函数等等，可以理解为把一个数据表自上而下、逐一（one by one）完成“转换”计算，从而生成一个全新的“逻辑列”（logical column）。PowerBI 中把这个逻辑列成为 Calculated Column——之所以是过去时 calculated，而非 calculating，重在表达它是预先的数据准备，一定先于后续的聚合分析而完成。 （2024-07-12补充）

比如，在Excel或者Power BI中，使用YEAR函数计算“年度”，从而完成“各年度的销售额总和”分析。如下图所示，YEAR会在每一行之后返回一个计算值（理论上每一行都可以截然不同）。

Year_order = YEAR( 'table_name'[Order_date] ) 

在数据分析过程中，几乎所有的数据准备工作，都是类似的计算逻辑。比如销售单价*销售数量、销售额*折扣率、发货日期-订单日期（发货间隔）、从产品名称中拆分「品牌」字段等等。

这些计算的共同特征是：（1）在数据表明细行中完成，每一行的计算完全独立、互不影响；（2）返回的结果是数据表，即多值。

从分析的角度看，此类计算可以称之为数据准备类计算（Data Preparing Calculations）；与之相对的则是则是以计数、求和为代表的分析型计算（Analytical Calculations）。这不仅仅是迭代计算的分类方式，也是函数的分类方式，更是计算的分类方式。

此为业务视角。

二、理解迭代的共同点：不能脱离行级别而存在

从上述的多个场景中，我们可以做出如下的总结：

1）迭代函数的共性是逐行访问数据表（即多个元组）的迭代过程（iterate the table row by row)；

  • 迭代的对象是列表或数据表，而且必须是整个表，不能只迭代其中的子集；
  • 如下图所示，展示了前面两类迭代函数的过程。Year 函数计算每一行的“年度”，逐一计算、互不影响；COUNT 计数计算整个表或每个字段的计数，逐一计算、互不影响。

2）迭代的环境必须是整个明细表，即迭代的计算结果要在明细上有意义，不能脱离明细而存在（关键）

对于 YEAR 等日期函数而言，计算结果必然是对应每一行的，结果无法脱离当前明细表而存在。但对于 COUNT 计数、SUM 求和等聚合函数而言，则会出现”结果在明细表中“和”脱离明细表自成新表“两种情形。

如下图所示，COUNT 计数既可以逐一记录在明细表的每一行中，也可以记录在透视表中。前者是迭代计数，后者不是迭代计数。

理解这一点，是理解“迭代”的关键。 迭代的关键不是被计算的对象，而是发起计算的”当前行“（Current Row）。

在高级问题中，问题中也会引用 SUMX 等聚合函数，此时会发生官方所说的“上下文转换”，即行上下文转换为筛选上下文。不过，我个人更喜欢从另一个角度理解。如下图所示，透视表中出现了一个全新的详细级别——问题的详细级别，即问题观察的视角，这里是“年度”。年度要对明细表完成分组，而后分别计数活得“各个年度的订单计数）。

既然没有了 行级别，也就没有了迭代，更没有”行级别上下文的转换“一说了。

从上述的多个场景中，我们可以做出如下的总结：

  • 迭代的对象可以是列表，也可以是数据表。而迭代次序默认是线性地逐个访问，对于列表LIST而言就是“逐一”one by one，对于数据表TABLE而言，就是“逐行”row by row 。直至迭代完整个迭代对象。
  • 迭代计算，就是在每个迭代元素处、执行的计算过程，可以形象的理解为“自上而下”和“自左到右”两种计算过程。它们对应两种迭代类型，如下所示：

最重要的两种迭代函数，其一是 sumx，其二是 filter。

特别补充：这里的SUMX聚合，是依赖于row level、row context的聚合，不同于依赖group by的聚合。我所讲的“聚合函数”，都是和group by结合的聚合——在最新的“sql 别裁新解”中，我称之为 “聚合表达式”，从而和 sumx分开。

从这个意义讲：

💡 只有行级别的聚合包含迭代过程，但迭代不一定是聚合计算。
💡 聚合表达式（SUM+GROUPBY）不是迭代计算，因为没有行级别计算，而是相对于视图详细级别（filter context）

POWER BI中，FILTER可以理解为for或者while判断，它扫描数据表、逐行判断筛选条件（filter condition），然后返回符合条件的数据表明细行，因此结果还是一个数据表。这里可以用列表来表示：

#  iteration 
for i in range(10): 
# print(i)
     if i > 5:
     print(i)

V1 Jan 9, 2023 喜乐君
V2 Jan 14, 2023 增加排序等
V3 Jan 18, 2023
V4 Jan 30, 2023 修改第一部分，发布知乎
V4.2 Mar 7, 2023 调整部分内容——仅限新博客
V 5 2024-07-12 修改图片；完全重写第二部分。 迭代的关键是理解当前行，逐一的重点不是计算的对象，而是当前行逐一。