在 Excel 中,我们习惯使用聚合函数一步一步地执行计算。比如为了计算总销售额,你创建了一列,用价格乘以数量,第二步,你将这一列求和以计算总销售额。这个数字会很有用,例如作为计算每个产品销售额占比的分母。
使用 DAX 的迭代函数,我们只需要一步就可以完成相同的操作。迭代函数完全按照它的名字表示的意思来执行:迭代整张表,并对表的每一行执行计算,最后聚合结果以生成所需的单个值,这类迭代函数使用的是聚合函数+X 的形式。
初识迭代函数
如果需要计算所有销售额的总和,有的用户可能会尝试写出这样的公式
[AllSales]:= SUM(Sales[ProductQuantity] * Sales[ProductPrice])
实际上,这是不被允许的,因为聚合函数只能使用单个列作为参数。你可以使用迭代函数 SUMX 计算所有销售额的总和,它逐行计算 Sales 表的销售额,将所有结果相加得到最终答案:
[AllSales] := SUMX ( Sales, Sales[ProductQuantity] * Sales[ProductPrice] )
这种方法既有优点也有缺点。好处是,你可以在单个步骤中执行许多复杂的计算,而不必为此添加许多列,这对某些特定的公式很有用。另一方面,由于使用 DAX 编程的视觉直观性不如 Excel。实际上,你观察不到那个将价格和数量相乘的虚拟列;它只短暂的存在于公式运行的时候。
你仍然可以选择创建一个计算列来计算价格和数量的乘积。然而,在某些情况下这不是一个好的做法,因为它占用了更多的内存,并可能会减慢模型刷新速度。
语法示例
聚合函数语法
Aggregate ( <ColumnName> )
迭代函数语法
AggreateX ( <Table>, <Expression> )
将某些聚合函数结尾加 X 可以构成迭代函数,迭代函数语法结构基本相同,都以相同方式工作,仅最后的聚合操作有所不同。具体如下:
- 在现有上下文中计算第一个参数,以确定要扫描的行。
- 为上一步计算的表的每一行创建一个新的行上下文。
- 迭代表并在现有计值上下文中计值第二个参数,现有上下文中包括新创建的行上下文。
- 聚合前一步中计算的值(SUMX 求和、MINX 取最小值、AVERAGEX 取平均值 以此类推)
常用的迭代函数
SUMX
对<Table>的每一行计算表达式<Expression>,将得到的所有结果求和。SUMX只考虑列中的数字,空白、逻辑值和文本将被忽略。
SUMX ( <Table>, <Expression> )
AVERAGEX
对<Table>的每一行计算表达式<Expression>,将得到的所有结果计算算术平均值。AVERAGEX 忽略空值。
AVERAGEX(<table>,<expression>)
MINX
对<Table>的每一行计算表达式<Expression>,从得到的所有结果中取最小值。MINX 支持数值,文本和日期类型,忽略空值,不支持逻辑值 TRUE/FALSE。
MINX(<table>, < expression>)
MAXX
对<Table>的每一行计算表达式<Expression>,从得到的所有结果中取最大值。MAXX 支持数值,文本和日期类型,忽略空值,不支持逻辑值 TRUE/FALSE。
MAXX(<table>,<expression>)
COUNTX
对<Table>的每一行计算表达式<Expression>,计算包含非空白值或计算结果为非空白值的表达式的行数。COUNTX 支持数值,文本和日期类型,不支持逻辑值,如果没有要计数的行则返回空值。
COUNTX(<table>,<expression>)
COUNTAX
对<Table>的每一行计算表达式<Expression>,计算包含非空白值或计算结果为非空白值的表达式的行数。COUNTAX 支持数值,文本和日期类型,支持逻辑值,如果没有要计数的行则返回空值。
COUNTAX ( <Table>, <Expression> )
PRODUCTX
对<Table>的每一行计算表达式<Expression>,将得到的所有结果相乘。PRODUCTX 仅支持数值,空白、逻辑值和文本将被忽略。
PRODUCTX(<table>, <expression>)
本文以 SUMX 为例介绍这类迭代函数。
与返回表的表达式的配合使用
除了直接使用表作为迭代函数的第一参数,你也可以使用返回表的表达式作为参数, 为表的每行计算 DAX 表达式。例如
[Sales Amount] := SUMX ( Sales, Sales[Quantity] * Sales[Unit Price] )
你可以使用其他表函数替换对销售表引用。例如, 你可以使用 FILTER 函数筛选数量大于 1 的销售记录:
[Sales Amount Multiple Items] := SUMX ( FILTER ( Sales, Sales[Quantity] > 1 ), Sales[Quantity] * Sales[Unit Price] )
在计算列中, 还可以使用 RELATEDTABLE 函数检索位于一对多关系多端的表的所有行。例如,以下产品表的计算列统计所有产品对应的销售金额:
Product[Product Sales Amount] = SUMX ( RELATEDTABLE ( Sales ), Sales[Quantity] * Sales[Unit Price] )
在基础函数类型 – 关系函数 一文中,可以找到 RELATEDTABLE 函数的详细说明。
你可以将对表函数的调用嵌套在同一个 DAX 表达式中,因为任何表表达式都可以是对表函数的调用。例如,在下面的产品表计算列中,只考虑数量大于 1 的销售记录,计算产品销售额。
Product[Product Sales Amount Multiple Items] = SUMX ( FILTER ( RELATEDTABLE ( Sales ), Sales[Quantity] > 1 ), Sales[Quantity] * Sales[Unit Price] )
控制计算发生的详细级别
聚合函数的计算默认发生在视觉级别上,计算的颗粒度始终与当前筛选上下文一致。以 X 结尾的迭代函数因为引入了表作为参数,可以通过对表的汇总级别的控制,调整计算发生的级别。
例如你可以按销售大区汇总产品销售金额,如果使用 SUM 函数,销售额将在大区级别进行汇总。如果使用 SUMX 函数,稍微修改第一参数,可以将计算级别下移至更细的城市颗粒度
= SUMX ( VALUES ( 'Table'[City] ), CALCULATE ( SUM ( 'Table'[Sales] ) ) )
目前这个公式的结果与原计算一致,而一旦你需要在大区级别显示各城市销售额总和的平均值 ,又不希望将城市显示到报告上,这种写法会非常有用。Tableau 详细级别表达式中的Include关键词也可以实现类似的效果。
创建行上下文
除了计算列之外,迭代函数也可以创建行上下文,这对于理解 DAX 计值流非常重要,在理解行上下文中对这部分知识做过详细介绍。
聚合函数的本质
聚合函数 SUM 与迭代函数 SUMX 看起来是两个不同的函数,实际上它们之间存在某种相似性,与之类似的 MIN 和 MINX、MAX 和 MAXX 等组合也具备这种相似性,理解这种相似性有助于你理解为什么聚合函数忽略行上下文,只考虑筛选上下文。也会加深你对 DAX 的理解。
迭代函数的性能问题
因为 SUMX 是一个迭代器,您可能认为 SUMX 本质上是低效的。但事实并不是这么简单。因为 DAX 引擎已经被优化到可以高效地处理迭代器。不过话虽如此,糟糕的 DAX 肯定会导致 SUMX 效率低下,原因是这样的:
= SUMX ( VALUES ( ‘Table'[City] ), CALCULATE ( SUM ( ‘Table'[Sales] ) ) )
高飞老师,这个公式中的values在这里是去重的意思吗
老师这里我有个疑问,我使用迭代函数这里为什么返回的是累加啊,我没搞懂,我认为都是返回100%,麻烦老师解答一下,谢谢。
= SUMX ( VALUES ( ‘Table'[City] ), CALCULATE ( SUM ( ‘Table'[Sales] ) ) )
老师好,请问这个是度量值,还是新建列?
本文中的聚合函数实际上是迭代函数的特殊形式,迭代函数在外部筛选上下文中计值,那如果外部没有筛选上下文,只有行上下文,是不是就变成在行上下文中计值。
COUNTAX
对的每一行计算表达式,计算包含非空白值或计算结果为非空白值的表达式的行数。COUNTX 支持数值,文本和日期类型,支持逻辑值,如果没有要计数的行则返回空值。
COUNTX改成COUNTAX
高飞老师你好,我在计算列中使用sumx这样的迭代函数,觉得结果和我自己的理解有偏差。
我有一个产品表,表的信息如图,
这是我添加的计算列里的表达式:SUMX(ALL(‘产品表'[产品类别]),CALCULATE(sum([产品数量]))),我对于这个表达式的理解是,首先ALL返的是一列的不重复产品类别的表,然后根据这个表去逐行计算[产品数量],CALCULATE(sum([产品数量]))这个公式不会考虑外面的行上下文,考虑的是内部的all返回的这一列的行上下文转化成的筛选上下文,那每一行的结果就应该是整个表的数量,但是结果却是每行都返回的1,请问我的理解错误在哪里?
老师,我还是理解不了迭代函数和行上下文,如果我有一张销售表包含{日期 店铺 款号 销量},如果我想添加一列[开售日期],该款最早在哪天产生销售?或是该店该款最早在哪天销售?
老师,CONCATENATEX第3参数我想输入换行符,怎么输入啊?
你好老师,
Product[Product Sales Amount] =
SUMX (
RELATEDTABLE ( Sales ),
Sales[Quantity] * Sales[Unit Price]
)
在这段代码中,由于是计算列,于是穿件了行上下文,而SUMX的第一个参数只在筛选上下文中计值,第一参数中的Sales应该是完整的Sales表。那么这里RELATEDTABLE是如何起作用的呢?因为它只作用于当前有效的行上下文而忽略筛选上下文吗?如果relatedtable是calculatetable的简化,但是SUMX的第一参数的计值上下文是筛选上下文,那么就没有行上下文传入relatedtable进行转换了。
Product[Product Sales Amount] =
SUMX (
RELATEDTABLE ( Sales ),
Sales[Quantity] * Sales[Unit Price]
)
老师,这里用RELATEDTABLE的意义是什么呢,如果对多端的数值列做聚合计算的度量值,都需要用这个函数吗?与下面这个表达式实质的计算结果区别是什么呀?
[AllSales] :=
SUMX (
Sales,
Sales[ProductQuantity] * Sales[ProductPrice]
)
高飞老师你好,我最近在求第n大(小)值得时候遇到了问题
我首先想到的是maxx这类函数,但是这一类函数只有两个参数,想要求第n大,就要在表参数或者表达式参数上想办法了。如图,我的办法是层层计算,比如要求第二大,就先求出最大,要求出第三大,就要先依次求出第一、二大。越到后面表达式越复杂,而且if嵌套太多也很影响计算效率。
然后我又想到了topn,比如我要求第四大,我的想法是先用topn求得最大得4项,再在这四项里取最小就是第四大了,但是实际的情况是topn有容错的特性,比如总共只有3项,topn(4)取的的就是3项,这个时候求最小值,就是第三大了,而不是想要的第四大,就加入了一个topn的countrows的判断,这下终于得到了想要的结果,如图。
我的疑惑就是好像暂时还没有一个函数来完美的满足这种需求的?都需要其他函数嵌套才能得到想要的结果,其实这种情况在实际中还挺多,比如求离最近一次记录间隔最短的的记录(就是时间第二大的记录)之类的。
麻烦老师解惑!
销售量Sumx := SUMX(‘销售表’,’销售表'[数量]),把这个度量值放入透视表中,只被门店ID所筛选,老师你上面讲的迭代函数运行过程的4个步骤中我给出前两个步骤的理解,第一个步骤中关于销售表的上下文是指各门店的销售表,而不是所有门店的销售表; 第二个步骤是SUMX在各门店的销售表中创建了行上下文,并逐行运行第二参数的表达式。这就是我看了老师您分析后的理解,请指点我的理解是否准确,并同时也恳请老师分析这些步骤的时候最好给个案例,以具体案例讲解每个步骤,便于我们这些初学者理解。
大神,聚合函数本质 等价说明部分,为什么说SUMX(’Table’,’Table'[Sales])中SUMX第一参数在外部筛选上下文环境中计值,忽略行上下文,按前面迭代函数的语法,会在Table上新建一个行上下文,,理解不了了
另外前后有表带单引号的有不带的应该都是表示的是一样的吧。
如果创建计算列,而MAXX(table,expression)类函数中的表达式又是table中的列,那其创建行上下文是什么?计算逻辑是啥样呢?