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

理解扩展表 Expanded Tables

扩展表(Expanded Tables)理论是 DAX 的核心,了解扩展表的工作原理对于理解 DAX 的至关重要,本文将系统介绍扩展表的相关知识。

在 DAX 的学习历程中,理解计值上下文、上下文传递、上下文转换筛选还原是非常重要的技能,你需要掌握这些技能才能准确理解 DAX 如何为表达式计值。但是之前的文章对计值上下文的定义做了一定程度的简化。因为在后续文章中,你会学到 DAX 计值上下文内部的全部细节。现在这个时刻终于来了:你即将开始学习计值上下文中最隐秘的知识。

在向你展示全貌之前,让我们先回顾一下迄今为止关于计值上下文的知识点:

  • 有两种上下文:行上下文和筛选上下文。
  • 行上下文不会通过关系传递。
  • 行上下文总是包含一行,它是由计算列或迭代函数引入的
  • 筛选上下文按照关系定义的方向传递。筛选上下文可以操作表,也可以操作列。当处理列时,它只筛选该列。当处理表时,它筛选表的所有列。

之前的所有描述都是正确的,到目前为止,你应该已经掌握了这些知识。然而,为了完成对计值上下文的彻底理解,你需要学习计值上下文和 DAX 的理论基石:扩展表。

注:如果你确信已经理解了扩展表理论,可以转到文末的测试题,检查一下自己的理解水平。

认识扩展表

模型中的每个表都有对应的扩展版本。扩展表包含原始表的所有列,以及可以通过多对一关系筛选原始表的所有表和列。让我们先用一个简单的模型来对扩展表有个直观的认识,下面的模型包含四张表,其中 Sales 表通过多对一关系可以访问所有表,所以它的扩展版本包含了整个模型。

Sales 的扩展表包含了模型中的所有表

现在让我们把模型变得稍微复杂一点,观察下图:

通过这个简单的模型你将学习扩展表的概念

同样的情况,Sales 表与 Product 表有多对一的关系,所以 Sales 表的扩展版本包含 Product 表的所有列,继续顺着关系推演,你很容易发现 Sales 表的扩展表包含了整个数据模型。另一方面,Product 表的扩展表包含自身, Product Subcategory 和 Product Category 的所有列。

日期表需要特别注意。事实上,它可以被销售表筛选是因为两者间建立了双向关系。而且这不是多对一关系,而是一对多关系。日期表的扩展表只包含日期表本身,即使日期可以被 Sales、Product、Product Subcategory 和 Product Category 表筛选。因为使筛选生效的机制是双向筛选而非扩展表。

对数据模型中的其他表重复相同的推演,你将创建这样一个扩展表对照。

每个表对应的的扩展表

扩展表是一个很有用的概念,因为它们为每个表显示了可以筛选自身原始表的列的集合。例如,如果以 Product 表为例,你可以轻松的发现如果你筛选了其扩展版本中的任何列,那么引擎也将筛选 Product 表。最重要的是,DAX 使用扩展表将所有筛选器置于筛选上下文中。为了更好地理解它,可以将模型的扩展表可视化到一个表格上,如图所示。

可视化展示让模型的扩展表更易于观察

该图表在行上列出了数据模型中的所有列,在列上展示了每个表。我们对单元格进行着色,以表示内部两种不同的列:

  • 原生列(Native columns):表的原始列
  • 相关列(Related columns):是沿着关系添加到扩展表的列。

列筛选器的一般规则

当你把一个筛选器置于一列,可以为包含该列的行涂上颜色,以直观地显示筛选了哪些表。如果你使用下面这个度量值

[RedSales] :=
CALCULATE (
    SUM ( Sales[Quantity] ),
    Product[Color] = "Red"
)

由于筛选器位于颜色列上,我们可以使用下图来突出显示包含 Product[Color]列的表

对列所在的行着色可以清楚地显示哪些表可以被筛选

颜色列的筛选器是一个列筛选器,即一个操作单列的筛选器。因此,我们现在可以声明列筛选器的一般规则:“来自列的筛选上下文可以筛选包含此列的所有扩展表”。此规则是对上图中所呈现出的规律的总结,但过于抽象,换一种说法是:来自列的筛选上下文可以筛选扩展表包含此列的表。正如你所看到的,这条规则与我们之前的表述相同,而当时我们还在谈论表和关系。实际上筛选上下文并不真正通过关系“传播”,它将筛选效果应用于包含该列的所有表,因为它基于扩展表生效。

颜色列的筛选器也会传递到日期表,尽管从技术的角度,颜色列不属于日期表的扩展表,这是双向筛选的作用,颜色列的筛选器并非通过扩展表到达日期表,DAX 引擎在内部注入特定的筛选代码以使双向筛选正常工作,而对扩展表的筛选则根据引擎的工作方式自动进行。两者的差异只存在于 DAX 内部,但指出这一点很重要。

表作为筛选器

你可以创建来自列的筛选器,也可以创建整张表作为筛选器,比如使用 FILTER 函数将整张表作为参数。那么表筛选器是如何工作的?他们也在扩展表上执行筛选。实际上,无论何时CALCULATE 中使用表作为筛选条件,筛选的都是对应的扩展表

为了理解这个概念,我们来看看这两个度量值:

[NumOfCategories] :=
COUNTROWS ( 'Product Category')

[NumOfCategoriesFilteredByProduct] :=
CALCULATE ( COUNTROWS ( 'Product Category' ), Product )

第一个度量值计算产品类别的数量。第二个计算相同的指标,但在此之前,它应用一个包含产品表的筛选上下文(当然,它会被外部上下文筛选)。结果参考下图,其中我们将产品颜色设为行标签。

透视表显示了在 CALCULATE 中应用表筛选器的效果

第一列 NumOfCategories 始终显示相同的值。原因在于行标签使用的是产品表颜色列,如果你查看之前的扩展表图解,产品类别表并不包含颜色列。因此,筛选颜色列对筛选上下文中可见的 NumOfCategories 没有影响。但是,当你将整个产品表作为筛选器参数时,你实际上使用的是产品表的扩展表。因为产品表的扩展版本包含产品子类别表的所有列,所以可见的 NumOfCategories 不再是 8 个,而是包含特定颜色的结果。

使用表作为 CALCULATE 的筛选器参数将筛选其扩展表的所有列,因此,以下两个指标存在显著不同:

[NumOfCategoriesFilteredByColor] :=
CALCULATE (
    COUNTROWS ( 'Product Category' ),
    FILTER (
        ALL ( Product[Color] ),
        Product[Color] = "Green"
    )
)

[NumOfCategoriesFilteredByProduct] :=
CALCULATE (
    COUNTROWS ( 'Product Category' ),
    FILTER (
        ALL ( Product ),
        Product[Color] = "Green"
    ) 
)

虽然这两个公式看起来几乎相同,但实际并非如此。第一个度量值只在颜色列上放置一个筛选器,因此,它的筛选效果被隔离到包含 Product[Color]列的扩展表中。因此,它对产品类别表没有影响。但是,第二个度量值在整个产品表上设置一个筛选器。因为产品表的扩展表包含了产品类别表的列,所以第二个度量值只计算包含绿色产品的类别的数量,而第一个度量值总是显示类别的总数。

透视表显示了在 CALCULATE 中应用表筛选器的效果

扩展表是帮助你理解筛选上下文传播方向的强大工具。实际上,它是 DAX 筛选上下文得以传播的真正理论基础。因为扩展表的生效方式不是很直观,理解它需要一定基础,所以我们在介绍它之前先介绍了其他概念,以帮助你熟悉该语言。一旦你掌握了扩展表的使用,就会发现理解 DAX 的工作原理并没有想象的那么难。

RELATED, RELATEDTABLE 和扩展表

扩展表包含了关系。实际上,关系就是在扩展表中运行的,一旦你开始用扩展表的方式思考,就不再需要考虑关系了。在刚开始学习 DAX 的时候,我们对 RELATED 的认知是它允许访问相关表中的列。更准确的理解是,RELATED 允许你访问扩展表的相关列。

变量和扩展表

关于扩展表有一个重要规则:扩展行为在定义表的时候发生。观察下面的查询:

DEFINE
    VAR SalesA =
        CALCULATETABLE ( Sales, USERELATIONSHIP ( Sales[Date], 'Date'[Date] ) )
    VAR SalesB =
        CALCULATETABLE ( Sales, USERELATIONSHIP ( Sales[DueDate], 'Date'[Date] ) )
EVALUATE
ADDCOLUMNS ( SalesB, "Month", RELATED ( 'Date'[Month] ) )

SalesA 和 SalesB 使用不同的关系定义 Sales 表。SalesA 使用 Sales[Date](默认关系),SalesB 使用 Sales[DueDate]的关系。ADDCOLUMNS 迭代 SalesB 并返回对应的’Date'[Month],问题是这个新增的月份列会使用哪个关系呢?Sales[Date]还是 Sales[DueDate]?如果你仍然从关系的角度思考,很容易被误导。

实际上,RELATED 访问的是 Sales 扩展表上的列。SalesB 包含 Sales 的扩展表,该扩展发生在 Sales[DueDate]作为活动关系的时候。因此,SalesB 中的 Date[Month]与 Sales[DueDate]有关,与 Sales[Date]无关。很显然,如果你改用 SalesA 进行迭代,将得到另外一种结果。

ALL 函数和扩展表

ALLEXCEPT 对表的所有列移除筛选器,除了作为参数的列之外。一个不太常见的用法是,你也可以在 ALLEXCEPT 中使用扩展表,例如,下面这个度量值可以正常计值:

[SalesOfSameColorAndCategory] :=
CALCULATE (
    SUMX (
        Sales,
        Sales[Quantity] * Sales[UnitPrice]
    ),
    ALLEXCEPT (
        Product,
        Product[Color],
        'Product Category'[Category]
    ) 
)

原因是产品表的扩展表也包含产品类别表的所有列。你还可以从扩展表中指定一个完整的表作为参数,而不是逐个指定表的所有列。例如,关于 ALLEXCEPT 的以下两个表达式是等价的:

ALLEXCEPT ( Product, 'Product Category' )
ALLEXCEPT ( Product, 
           'Product Category'[ProductCategoryKey], 'Product Category'[Category] )

第一个公式的含义是,在第二参数中指定的表被排除在 ALL 函数的效果之外。前提是这些表必须是第一参数扩展表的一部分。

CALCULATE 调节器和扩展表

ALL 作为 CALCULATE 调节器时,不返回表,只从作为参数的表的扩展版本中移除筛选器。观察以下公式:

[NumberOfSales]:= CALCULATE ( COUNTROWS ( 'Date' ), Sales  )
[NumberOfSalesWithAll]:= CALCULATE ( COUNTROWS ( 'Date' ), ALL ( Sales ) )

两种写法唯一的区别是,后者在 CALCULATE 的筛选器参数中使用了 ALL。如果 ALL 返回 Sales 表的所有行,那么这两个度量值将以相同的方式运行。然而,事实并非如此,ALL 在这里的作用是移除筛选器。在计值 COUNTROWS 之前,ALL 从当前筛选上下文中移除 Sales 表的所有列,也就是移除了来自整个模型的所有筛选器,所以 Date 表无法被任何筛选器筛选,NumberOfSalesWithAll 的结果不是有销售记录的日期数。相反,它返回日期表中的日期总数。

扩展表的性能提示

本文介绍了扩展表理论,当你真正理解它之后,可以借助它读懂并解释很多之前难以理解的公式,也可以开始试着对度量值和查询的写法做出优化。通过将理论付诸实践,你的 DAX 水平会得到进一步的提升。

另外,通过阅读本文,你不难发现一种新的筛选器用法:直接将表作为 CALCULATE 的筛选器参数,比如上文中的[NumberOfSales]公式。这种写法简单粗暴,事实证明在某些情况下也确实非常有效,但我仍然建议你谨慎使用,原因是:

本文隐藏内容查看价格为8G币,请先
单独购买的内容长期有效,不受时间限制(购买前先刷新当前页面)。加入VIP会员可享受全站权益,性价比更高。

扩展表理解测试

在学习 DAX 的过程中,掌握扩展表理论非常重要,因为它会帮助你理解公式背后的理论细节,这对于理解正确和错误的度量值之间的细微差别至关重要。

第一题

如图所示,两张表通过 manager 建立关系,在左边新建计算列[SUM],计算每个经理的销售额,如图所示,公式得到了正确的结果。

第一题的示例数据和公式

如果你按关系的方式思考,CALCULATE 将维度表的行上下文转换为筛选上下文,这些筛选条件进入模型,沿着关系筛选了事实表,进而得出最终结果。现在的问题是,公式用 ALL 移除了组成 sales 表的四列,也就移除了施加在这些列上的筛选器,计算列的每行似乎应该得出相同的结果,但事实并非如此,你能说出原因吗?

本文隐藏内容查看价格为8G币,请先
单独购买的内容长期有效,不受时间限制(购买前先刷新当前页面)。加入VIP会员可享受全站权益,性价比更高。

第三题

想象有这样一个两张表的简单模型,Answers 表和 Customers 表通过 CustomerKey 列建立关系,Customers 位于关系的一端,Answers 位于多端。基于扩展表原理,我们计划将 Answers 表作为筛选器计算顾客数量,同时增加一个筛选器 Answers[AnswerKey] = 6,希望只计算回答特定问题的顾客数:

CALCULATE (
      DISTINCTCOUNT ( Customers[CustomerKey] ),
      Answers,
      Answers[AnswerKey] = 6
)

问题:将这个公式放入卡片图,它将返回以下哪种结果,为什么?

  • A 空值
  • B 所有客户数
  • C 有答题记录的客户数
  • D 回答 AnswerKey 为 6 这个问题的所有客户数

本文隐藏内容查看价格为8G币,请先
单独购买的内容长期有效,不受时间限制(购买前先刷新当前页面)。加入VIP会员可享受全站权益,性价比更高。

277
说点什么

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

老师,第二题我有些没理解过来,我用Excel实践了一下,得到的结果有些问题。麻烦老师看一下。详情在图片里。谢谢老师

1707176969429.jpg
成员
似水无痕

高老师,麻烦问个《DAX权威指南》第15章 高级关系-多对多关系-通过公共维度实现多对多关系(P479~P483) 的问题:
在DAX Studio中运行图1和图2中的两个查询,得到的结果如下:
1、查询1的结果看出,迭代表 VALUES(Budget[CountryRegion]) 不具备 [Sales Amount] 的数据沿袭
2、查询2的结果看出,迭代表 Budget 却具备了[Sales Amount] 的数据沿袭
这是为什么?
以下是我的理解:
1、当对表 VALUES(Budget[CountryRegion]) 进行迭代时,隐式的CALCULATE将行上下文转换为筛选上下文,但来自Budget[CountryRegion]列的筛选上下文仅筛选了Budget表。
2、当对表Budget进行迭代时,此时的筛选上下文是使用了Budget表的扩展表,而扩展表包含了Brands表和CountryRegions表,因此具备了[Sales Amount]的数据沿袭。
比如红色框标识的结果行,实际在上下文转换时,筛选上下文不但包含了Budget的三个列的值,还包含了 Brands[Brand]=“A. Datum” && CountryRegions[CountryRegion] = “China”这两个筛选条件,因此“金额”列具备了[Sales Amount] 的数据沿袭。
不知道我的理解对不对?请老师指正!

03.png
02.png
成员
chen1997

请教高老师一个扩展表问题,数据模型,矩阵如图所示,我想计算的就是城市公司包含在商品名称表的配件数,商品名称表的配件数总和是406,为什么把
每个公司需要的配件数 = CALCULATE(sum(‘商品名称'[配件数]),’销售明细’)
托到行是城市公式的矩阵里面也是406,里面不是涉及到扩展表吗?
我的想法;按住扩展表原理,我用事实表作为calculate的筛选条件,按理说应该是筛选整个模型,我用城市公司作为矩阵的行,城市公司会对事实表销售明细进行筛选,然后在此基础上得到的表在筛选商品名称的表,最后在计算配件数,请问我的思路是哪里有问题吗

01d7211d46abd0ca329caf4811b18ff.jpg
714f7f9e55d0ffda9aa3fa538f49cee.jpg
成员
此夕洛溯

请问老师,测试第三题中,“第三行的 Answers 表基于扩展表生效,包含 Customers 的所有列,可以筛选 Customers[CustomerKey]”, Customers 表中没有答题记录的客户是在这一步被筛选掉的吗?如果是的话,那原因是:Answers 表的原生列Answers[CustomerKey]会影响到Answers 表的扩展表的列Customers[CustomerKey]的值吗?这样的话,感觉是多端筛选了一端。望老师解惑,谢谢

成员
jb870610

高老师,有个扩展表的题目困扰很久,百思不得其解,恳请帮忙分析一下。数据模型的3张表和关系如图1所示,为什么多端表成本表–利润计算列的公式,当中的CALCULATE部分,只有一 参,并没有表筛选器,却可以正确筛选销售表的销售数量,能不能像第二道题一样,详细的解说一下其原理,比如CALCULATE 计值流、上下文转换和扩展表原理等等,是怎样一步一步生成正确的结果的? 作揖 作揖 作揖

2.png
1.png
成员
jb870610

第二题:“为了消除扩展表的影响,最简单的方法是恢复列作为筛选器”,列筛选器不是表筛选器的语法糖吗?如果是的话,列筛选器是否也包含它的扩展版本?

微信图片_20230628203922.png
成员
jb870610

第一题CACULATE的二参筛选器是算列筛选器还是表筛选器啊?

成员
你猜

老师,第一题的all里面写了两个’sales'[manager],有一个应该是’sales'[amount]。
我是这么理解的:
公式:CALCULATE(SUM(‘salses'[amount]),ALL(‘salses'[manager],’salses'[amount],’salses'[order],salses[ordertype]))
第一行:首先calculate,将行上下文转换为筛选上下文,筛选条件是manager=”john”,department=’south’,有两个筛选器 , 然后all去除了’sale’四个原生列的筛选,本身也没有对四个列进行筛选。然后上面的六个筛选器取交集。交集的结果是manager=”john”,department=’south’。求出结果是4。

公式:CALCULATE(SUM(‘salses'[amount]),ALL(‘salses’))
第一行:首先calculate,将行上下文转换为筛选上下文,筛选条件是manager=”john”,department=’south’,有两个筛选器。
ALL(‘salses’)去除的筛选器,不只有’sales’的原生列,还有’sales’的相关列,对于本例,也就是去除了所有的筛选器。
所以结果都是12。

不知道我理解的是否正确?

expand.jpg
成员
小本本

老师这里红框中, 应该是”不是多对多关系”吧

Snipaste_2023-04-08_17-16-49 (小).png
游客
静听溪流

采购表:日期、水果名、重量
信息表:编号、价格、水果名

度量值 = CALCULATE(sum(‘信息表'[价格]))
度量值 2 = CALCULATE(sum(‘信息表'[价格]),’采购表’)
度量值 3 = CALCULATE(sum(‘信息表'[价格]),’采购表'[水果名]=”苹果”)

矩阵行:采购表的水果名
值:以上三个度量值

结果:度量值和度量值3 ,每个水果名对应的结果都是信息表中的价格总和 ;度量值2 ,对应信息表中该水果名的价格
“来自列的筛选上下文可以筛选包含此列的所有扩展表”

那么请问以上的度量值和度量值3 为什么无法筛选水果名列对应的拓展表(信息表)?

本想上传图片,但不知道该怎么上传。

游客
chen1997

老师请问在这个例子中(ALLEXCEPT ( Product, ‘Product Category’ )
ALLEXCEPT ( Product,
‘Product Category'[ProductCategoryKey], ‘Product Category'[Category] ))不是说选择了表的所有列就等同于选择了这个表,为什么在案例1中all(sales表所有列)不等价于all(sales)

成员
做一名学霸

变量和扩展表
关于扩展表有一个重要规则:扩展行为在定义表的时候发生。

老师好,定义表的时候,是指什么时候~~~

成员
151****9937

请高老师帮我看下,谢谢。
存在的问题:表作为筛选器时(也即包括了扩展表的所有列),根据扩展表理论,应该会覆盖外部相同列上的筛选器,而实际没有。不知道是什么原因?

1666410752237.png
成员
839838408

老师您好,针对第三题,我有如图的理解,麻烦您过目,谢谢!

Capture.PNG
成员
小本本

老师, 文章中allselect是对扩展表的复杂筛选还原成标准筛选了吗?

Snipaste_2022-07-23_12-58-10.jpg
成员
暮色

大佬帮忙看下这个图片上的问题 总感觉有哪里不太对劲??

111.jpg
成员
优雅野人

这么重要的一章,可是没有案例文件,之前下的几个案例都不适用。强烈建议补充上。

成员
PBI研究

精辟 我的DAX水平直线上升 谢谢老师 非常感谢您

成员
芒果

我冒昧再咨询一个问题哈。多谢高老师。

在CALCULATE调节器和扩展表一节的两个公式如下:
[NumberOfSales]:= CALCULATE ( COUNTROWS ( ‘Date’ ), Sales )
[NumberOfSalesWithAll]:= CALCULATE ( COUNTROWS ( ‘Date’ ), ALL ( Sales ) )

我通过PBI DESKTOP测试,发现结果都一样的。如下图。不知道为什么。

理解扩展表3.jpg
成员
芒果

高老师,我对下面这段话里面个别地方不明白。已经标注在里面了。

“扩展表是一个很有用的概念,因为它们为每个表显示了可以筛选自身原始表的列的集合(这个 集合里面不只包括自身原始表,还有关联表的列才对)。例如,如果以 Product 表为例,你可以轻松的发现如果你筛选了其扩展版本(这个扩展版本指的是什么?是包含了category,subcategory,product三个表?)中的任何列,那么引擎也将筛选 Product 表。(为什么这里专门指出引擎也将筛选product表?本来就是可以筛选category,subcategory,product三个表的啊。何止这一个表呢?)”
————————–

有时候定义不太明确,导致后面理解混乱不堪了。建议在容易导致歧义的地方,请加注解释一下。比如可以举个例子说明。让小白们不要猜测意思。

DAX 圣经

导读

初识 DAX

DAX 基础知识

DAX 原理

DAX 高级原理

基础函数类型

迭代函数

CALCULATE 函数

CALCULATE 调节器

基础表函数

条件判断函数

查找匹配函数

时间智能函数

统计类函数

投影函数

分组/连接函数

集合函数

其他函数