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

行上下文嵌套和EARLIER

了解行上下文嵌套

同一张表有多层嵌套的行上下文似乎很少见,但实际上这种情况经常发生。让我们用一个例子来解释这个概念。假设你想针对每个产品计算价格高于它的其他产品的数量。本质上这将根据价格对产品进行排序。

为了解决这个问题,我们使用 FILTER 函数,FILTER 是一个迭代器,它迭代表的所有行,并返回一个新表,其中只包含满足第二参数的行。例如,如果要检索价格高于 100 美元的产品列表,可以使用:

= FILTER ( Product, Product[UnitPrice] > 100 )

细心的读者会注意到,FILTER 需要具备迭代功能,因为只有当产品表存在有效的行上下文时,才能计算表达式 Product[UnitPrice]>100。否则单价的有效值将是不确定的。FILTER 的确是一个迭代函数,它为第一个参数中的表的每一行创建行上下文,从而可以在第二参数中计算条件。

现在让我们回到原来的问题:创建一个计算列,对那些比目前产品价格更高的产品计数。如果将当前产品的价格命名为PriceOfCurrentProduct,就很容易理解下面的伪 DAX 公式将满足你的需求:

Product[UnitPriceRank] =
COUNTROWS (
    FILTER (
        Product,
        Product[UnitPrice] > PriceOfCurrentProduct
    )
)

FILTER 将筛选出那些比当前产品价格更高的产品,且 COUNTROWS 对那些由 FILTER 返回的表中的行的数目进行了统计。剩下唯一的问题是如何用有效的 DAX 语法来替换 PriceOfCurrentProduct,来表达当前产品价格(所谓「当前」,意思是计算列的当前行),这可能比你想象的要难。

EARLIER 出场

我们在产品表中定义这个新的计算列。因此,DAX 将在行上下文中对表达式求值。但是,表达式使用了 FILTER 函数在同一个表上创建了一个新的行上下文。实际上,在前一个表达式的第 5 行中使用的 Product[UnitPrice]是由 FILTER 迭代的产品表的当前行的单价,这是最内层的迭代。因此,这个新的行上下文隐藏了计算列引入的产品表的原始行上下文。你看到问题了吗?你希望访问单价的当前值,但不要使用最后引入的行上下文(FILTER 迭代的那个)。相反,你希望使用之前的行上下文,即计算列中的那个。

DAX 提供了一种使其成为可能的函数:EARLIEREARLIER 使用前一个行上下文而不是最后一个行上下文检索列的值。因此,你可以使用 EARLIER (Product[UnitPrice])来表示PriceOfCurrentProduct的值。

EARLIER 语法

EARLIER ( <ColumnName>, [<Number>] )

返回<ColumnName>列在外部,第<Number>层行上下文对应的值,其中<Number>是可选参数。

EARLIER 是 DAX 中最特立独行的函数。许多用户之所以对 EARLIER 感到害怕,是因为并未按照行上下文来思考,也没有考虑过行上下文可通过对同一表格创建多个迭代而实现嵌套这一事实。在现实中 EARLIER 是一个简单且有用的函数,且可变得熟能生巧。解决该问题的代码如下:

Product[UnitPriceRank] =
COUNTROWS (
    FILTER (
        Product,
        Product[UnitPrice]
            > EARLIER ( Product[UnitPrice] )
    )
) + 1

在下图中,你可以看到产品表中定义的计算列,它使用单价的降序排序。

UnitPriceRank 列是演示 EARLIER 如何在嵌套行上下文中导航的示例

因为单价相同的产品有十四种,所以排名都是 1;第十五种产品排名为 15,与其他产品价格相同。建议你仔细研究和理解这个小示例,因为这是一个非常好的测试,可以检查你使用和理解行上下文的能力、如何使用迭代器(在本例中为 FILTER)创建行上下文,以及如何通过 EARLIER 从外部访问自身的值。

EARLIER 第二参数

EARLIER 接受第二参数,即要跳过的层数,这样你就可以跳过两层或多层行上下文。此外,还有一个名为 EARLIEST 的函数,它允许你直接访问表的最外层行上下文。老实说,EARLIEST 和 EARLIER 的第二个参数都不经常使用:虽然有两个嵌套的行上下文是常见的场景,但是有三个或更多的行上下文很少发生。

图解多层行上下文     图片:exceleratorbi.com.au

只有在同一种行上下文存在嵌套的时候才需要 EARLIER。如果 A,B,C,D 分别来自不同的表,你可以直接引用它们的列值,不需要使用 EARLIER

在结束这个示例之前,值得注意的是,如果你想将结果转换为一个更合理的排序(排名从 1 开始,之后每个名次加 1,即创建一个序列 1,2,3…),只要对价格计数而不是产品就可以了。这时,你可以借助 VALUES 函数:

Product[UnitPriceRankDense] =
COUNTROWS (
    FILTER (
        VALUES ( Product[UnitPrice] ),
        Product[UnitPrice]
            > EARLIER ( Product[UnitPrice] )
    )
) + 1

UnitPriceRankDense 提供了更理想的排名,因为它计算的是价格,而不是产品

EARLIER 的使用建议

EARLIER 是一个作用比较抽象的函数,当你掌握了变量的用法之后,EARLIER 函数就可以被完全替换掉了。但是从理解多层行上下文的角度出发,我仍然建议你彻底地学习和理解 EARLIER,尤其是初学者。

定义变量(VAR)来代替 EARLIER 的好处是会使代码更易于阅读。例如,你可以使用以下表达式代替之前的计算列:

Product[UnitPriceRankDense] =
VAR CurrentPrice = Product[UnitPrice]
RETURN
    COUNTROWS (
        FILTER (
            VALUES ( Product[UnitPrice] ),
            Product[UnitPrice] > CurrentPrice
        )
    ) + 1

在这个示例中,通过定义变量,将当前单价存储在 CurrentPrice 中,并在稍后使用该变量来执行比较。为变量命名,可以使代码更易于阅读,而不必在每次阅读表达式时都通过遍历行上下文层级才能理解计值流。

EARLIER 只能用于计算列吗?

虽然我们通常都是在计算列中使用 EARLIER,但并不意味着 EARLIER 只能用于计算列,实际上只要存在多层行上下文都可以使用 EARLIER。只不过计算列因为自身提供行上下文,只需要再使用一个迭代函数即可实现两层行上下文,而度量值则需要嵌套两层迭代函数才能构建出 EARLIER 需要的环境,操作起来稍显繁琐,但是这种嵌套对于深入理解行上下文很有帮助,让我们通过下面这个案例介绍这两种用法:

案例原始数据

原始表包含 date、最大步骤 id 和用户 id 三列,最大步骤 id 代表完成的步骤数量,值越大说明该用户在当前日期完成的步骤越多,比如最大步骤 id=4 说明用户已经完成了步骤 1,2,3,4。

现在要求按天统计完成每个步骤的用户数,也就是只考虑来自 date 列和最大步骤 id 列的筛选器,

  1. 对于计算列使用的公式,需要注意忽略来自用户 id 的筛选
  2. 对于度量值,我们默认透视表已经提供了这两列作为外部筛选上下文,只需要在度量值中构建出双层上下文即可
通过人数 =
CALCULATE (
    COUNTA ( '表 1'[用户 id] ),
    FILTER ( '表 1', [步骤 id] >= EARLIER ( [步骤 id] ) && '表 1'[date] = EARLIER ( [date] ) )
)
通过人数 _VAR = 
VAR x = '表 1'[最大步骤 id]
VAR y = '表 1'[date]
RETURN
    CALCULATE (
        COUNTA ( '表 1'[用户 id] ),
        FILTER ( '表 1', [最大步骤 id] >= x && '表 1'[date] = y )
    )
通过人数 _EARLIER:=
AVERAGEX (
    ADDCOLUMNS (
        '表 1',
        "COUNT", CALCULATE (
            COUNTROWS ( '表 1' ),
            FILTER (
                ALL ( '表 1' ),
                '表 1'[最大步骤 id] >= EARLIER ( '表 1'[最大步骤 id] )
                    && '表 1'[date] = EARLIER ( '表 1'[date] )
            )
        )
    ),
    [COUNT]
)
通过人数 _VAR :=
AVERAGEX (
    ADDCOLUMNS (
        '表 1',
        "COUNT",
        VAR x = '表 1'[date]
        VAR y = '表 1'[最大步骤 id]
        RETURN
            CALCULATE (
                COUNTROWS ( '表 1' ),
                FILTER ( ALL ( '表 1' ), '表 1'[最大步骤 id] >= y && '表 1'[date] = x )
            )
    ),
    [COUNT]
)
通过人数 _ 推荐写法 :=
CALCULATE (
    COUNT ( [用户 id] ),
    FILTER (
        ALL ( '表 1'[最大步骤 id] ), '表 1'[最大步骤 id] >= MAX ( '表 1'[最大步骤 id] )
    )
)

两种写法结果对比

度量值写法通过两个高亮的迭代函数 ADDCOLUMNS 和 FILTER 构建了两层行上下文,使得 EARLIER 可以正常计值,内层度量值[COUNT]为表 1 的每行计算通过人数,由于这里的表 1 已经被透视表的 date 列和最大步骤 id 列筛选,如果筛选后的表存在多行,[COUNT]将得到相同的结果,所以外层使用 AVERAGEX 取平均以确保获得准确结果。

度量值的前两种写法是出于演示 EARLIER 的目的,故意将公式复杂化,实际上如果只是解决问题本身,你完全可以用更简单的写法,参考最后一个度量值。需要指出的是,两者在明细行结果相同,总计行稍有不同:前两种写法在总计行计算的是整体的平均值,而最后一个写法只考虑最大步骤 id 一个筛选条件,这通常是没有意义的。

注:你可以在文章末尾下载到这个案例的源文件

小测试

测试表两个计算列的结果

计算列 1 = COUNTROWS(FILTER('测试表',EARLIER('测试表'[月份])='测试表'[月份]))

计算列 2 = COUNTROWS(FILTER('测试表',EARLIER('测试表'[月份])="1 月"))

基于上面的公式,你认为这两列的结果是什么,原因是?

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

100
说点什么

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

反复看,看懂了就想抽一根烟,太爽了!!!

成员
ElvisLee

老师,最后的习题看了历史讨论后,经过一番消化后,我大致描述下我的理解计算逻辑,望指教:
1、在原表每行后的对应单元格生成一个虚拟表,这个虚拟表等同于原表
2、使用FILTER对每个虚拟表进行筛选,筛选的条件为:EARLIER(‘测试表'[月份])=”1月”,此处因为EARLIER返回的是原表的当前行的”1月”,所以FILTER的结果都是TURE;而进行到原表第二行时,EARLIER(‘测试表'[月份])的结果变为“2月”,筛选的条件:EARLIER(‘测试表'[月份])=”1月”,返回结果均为False;以此类推,对原表后12个单元格生成的虚拟表逐一进行筛选;
3、COUNTORWS统计筛选后的虚拟表的行数,返回对于单元格,显示结果

如果原表新增“13月”、“14月”、“15月”,则计算的结果也随之变为:13、14、15

理解.png
成员
159****9804

老师我有些不明白,案例中在计算列时,用COUNTA(USER ID),FILTER (‘表’,…),而在计算度量值时,用的是COUNTROWS(‘表“,FILTER(ALL(‘表”,…),后者为什么要加上ALL呢?如果我理解的是,COUNT计数表或者列和filter 中的表和列统一,那么在推荐写法中,用的 COUNT ( [用户 id] ),
FILTER (
ALL ( ‘表 1′[最大步骤 id] ),
这里的ALL ( ‘表 1′[最大步骤 id] ), 和 COUNT ( [用户 id] ),又不一样了。
我不太理解filter 何时应该和ALL 联用
另外还有计数时,何时该用countrows, 何时直接用count呢?

成员
Soolang

请教一个问题.通过财务序时账中的含有物料部分的数据制作的进销存报表,但物料若某月份内没有发生过移动,这个月就无数据.问题是此时如何显示上个月的期末数据.目前是通过EARLIER查询<=当前期间的数据进行的查试,含有明细和汇总

成员
spongebill

有个疑问,最后那个推荐度量值写法里面条件是数值所以用了Max(),如果条件换为两个文本相等是不是应该用firstnonblank?
ALL ( ‘表 1′[最大步骤 id] ), ‘表 1′[最大步骤 id] >= MAX ( ‘表 1′[最大步骤 id] )

成员
139****3194

通过人数 _VAR :=
AVERAGEX (
ADDCOLUMNS (
‘表 1’,

老师,这里究竟是先根据addcolumns生成表1基础上追加列后的表再被外部上下文筛选,还是先被外部上下文筛选表1后再追加列,还是前后效果一样?

成员
139****3194

通过人数 _EARLIER:=
AVERAGEX (
ADDCOLUMNS (
‘表 1’,
“COUNT”, CALCULATE (
COUNTROWS ( ‘表 1’ ),
FILTER (
ALL ( ‘表 1’ ),
‘表 1′[最大步骤 id] >= EARLIER ( ‘表 1′[最大步骤 id] )
&& ‘表 1′[date] = EARLIER ( ‘表 1′[date] )
)
)
),
[COUNT]
)
高老师,averagex的第2参具有沿袭还是不具备?假设未来要调用它,谢谢

成员
139****3194

高老师,度量值推荐写法
FILTER (
ALL ( ‘表 1′[最大步骤 id] ),这里all返回所有不重复的动态列值,然后表1被日期列继续筛选形成动态列值,这两个列值取交集行,对吗

成员
139****3194

Product[UnitPriceRankDense] =
COUNTROWS (
FILTER (
VALUES ( Product[UnitPrice] ),
Product[UnitPrice]
> EARLIER ( Product[UnitPrice] )
)
) + 1
高老师,filter第一参数values表,这个表是虚拟的表,还是真实的表?这个表会跟原本的product默认建立1对多的关系吗?这个表的名字是什么?

成员
风轻云淡

看来大家到这都蒙圈了,评论留言比正文还长。我个人理解earlier函数有点像盗梦空间,构造一层一层的上下文,钻进去还得想办法出来,好烧脑啊,迷糊中。。。。。

成员
过路者

你好老师:filter是否,是将原来的列,合并earlier生成的虚拟列,生成一个笛卡尔积表进行上下文扫描的,我这样理解对吗

成员
wxb2012

通过人数 _ 推荐写法 := CALCULATE ( COUNT ( [用户 id] ), FILTER (ALL ( ‘表 1′[最大步骤 id] ), ‘表 1′[最大步骤 id] >= MAX ( ‘表 1&… 阅读更多 »

成员
莫塔塔

老师,请教下,我在其他地方看到有说earlier可以理解成当前行,但是用于判断测试中的计算列2,好像不太对,是那种理解有问题还是我个人理解的问题,快晕了。。

成员
159****1108

老师,看了您下面的回复,小测试计算列2里面,在第一行,“计算列执行的是 COUNTROWS(FILTER(‘测试表’,”1 月”=”1 月”)),所以结果是12”,还是没明白为什么结果是12,难道是迭代了12次吗?

游客
Lily

老师,度量值earlier计值步骤:
0. 最早执行先是addcolumn 第一参数被透视表筛选
1.先用addcolumns构造新表,含count字段列;
2. 该字段列值通过calculate 将1步骤表中行上下文转换为筛选上下文;
3.filter第一参数all覆盖转换筛选上下文;
4. Filter执行迭代,根据第二参数条件返回表形成筛选上下文;
5.然后calculate计值
6.最后,结合addcolumns表,用averagex迭代求count平均

老师,以上计值流哪处有错误?谢谢

成员
139****3194

老师,当内层有迭代行上下文,外层有筛选上下文,且作用同一张表,先执行外层还是内层?

成员
teddy_19_ajie

研究了一下小测试,感觉把计算列 2的公式这样调整一下就更好理解了:
计算列 2 = COUNTROWS( FILTER( ‘测试表’, “1月” = EARLIER( ‘测试表'[月份] ) ) )

成员
客家农民

最后一个小测试,在计算列的第一行,公式内的 FILTER 迭代的表每行都返回 True,这里不理解,还有静态布尔表达式也不太清楚,麻烦老师帮忙解答一下

成员
153****9010

练习1:=var x =MAX(‘表1′[date])
var y=Max(‘表1′[最大步骤id])
return CALCULATE(counta(‘表1′[用户id]),filter(all(‘表1’),’表1′[date]=x && ‘表1′[最大步骤id]>=y))
这个可以得到同样的结果,但是 度量值—推荐写法 却不太理解,尤其是 >= MAX ( ‘表 1′[最大步骤 id] 的环境。

成员
心之火焰

看完恍然大悟,之前对行间偏移引用一直百思不得其解。 鼓掌

DAX 圣经

导读

初识 DAX

DAX 基础知识

DAX 原理

DAX 高级原理

基础函数类型

迭代函数

CALCULATE 函数

CALCULATE 调节器

基础表函数

条件判断函数

查找匹配函数

时间智能函数

统计类函数

投影函数

分组/连接函数

集合函数

其他函数