从数据到信息
从信息到洞察

以X结尾的迭代函数

在 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 可以构成迭代函数,迭代函数语法结构基本相同,都以相同方式工作,仅最后的聚合操作有所不同。具体如下:

  1. 在作为第一参数接收到的表上创建一个新的行上下文
  2. 对于表中的每一行,在新创建的行上下文内部对第二个参数计值(也包括在迭代开始前已经存在的其他上下文)。
  3. 对步骤 2 中计算出的值进行相应的聚合,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>,计算包含非空白值或计算结果为非空白值的表达式的行数。COUNTX 支持数值,文本和日期类型,支持逻辑值,如果没有要计数的行则返回空值。

COUNTAX ( <Table>, <Expression> )

PRODUCTX

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

PRODUCTX(<table>, <expression>)

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

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

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

除了直接使用表作为迭代函数的第一参数,你也可以使用返回表的表达式作为参数, 为表的每行计算 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]
)
当发生表函数的嵌套调用时, DAX 首先计算最内层函数, 然后逐级计算到最外层。不要将此规则与函数调用参数时的计算顺序混淆。

控制计算发生的详细级别

聚合函数的计算默认发生在视觉级别上,计算的颗粒度始终与当前筛选上下文一致。以 X 结尾的迭代函数因为引入了表作为参数,可以通过对表的汇总级别的控制,调整计算发生的级别。

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

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

目前这个公式的结果与原计算一致,而一旦你需要在大区级别显示各城市销售额总和的平均值 ,又不希望将城市显示到报告上,这种写法会非常有用。Tableau 详细级别表达式中的Include关键词也可以实现类似的效果。

创建行上下文

除了计算列之外,迭代函数也可以创建行上下文,这对于理解 DAX 计值流非常重要,在理解行上下文中对这部分知识做过详细介绍。

聚合函数的本质

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

此处为隐藏内容 VIP会员和付费用户可见

迭代函数的性能问题

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

此处为隐藏内容 VIP会员和付费用户可见

 

下载面板

以上隐藏内容查看价格为3G 币,请先
注:加入 VIP 会员可享受全站权益,性价比更高。单独购买的内容长期有效,不受时间限制。

相关推荐

10
说点什么

avatar
1000
 
鼓掌微笑开心憧憬爱你色并不觉得吃瓜doge二哈喵喵思考笑哭捂脸悲伤大哭抓狂汗偷笑打脸捂眼黑线问号晕拜拜闭嘴衰咒骂ok作揖
1 评论数
9 被回复的评论
1 订阅评论的人数
 
查看最近回复
查看最热评论
  订阅本文评论  
最新 最旧 得票最多
提醒
liu
游客
liu

如果创建计算列,而MAXX(table,expression)类函数中的表达式又是table中的列,那其创建行上下文是什么?计算逻辑是啥样呢?