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

理解扩展表 Expanded Tables

扩展表(Expanded Tables)理论是 DAX 的核心,了解扩展表的工作原理对于理解 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] :=
CALCULATETABLE (
    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]公式。这种写法简单粗暴,事实证明在某些情况下也确实非常有效,但我仍然建议你谨慎使用,原因是:

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

扩展表理解测试

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

第一题

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

第一题的示例数据和公式

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

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

第三题

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

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

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

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

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

27
说点什么

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

请问老师:再向您请教一个问题,我做了一次简单测试,对结果不明白,创建1个表text,里面只有1列,列名为A,有且仅有这么1列,有7行数据,分别为0、1、2、3、4、5、6,然后设置一个度量值C,公式为CALCULATE(COUNT(‘text'[A])),结果为7,然后再设置一个度量值D,公式为CALCULATE(COUNT(‘text'[A]),’text'[A]),然后结果为6,研究了半天也不知道为什么,如果把count改成sum,C和D的值都为21,证明再数数的时候,度量值D没数值为0的第一行,这是为什么呢?(这个表没有和其他任何表有任何关系,也没有被筛选)

成员
139****3605

请问老师:第三题,“第四行的 Answers[AnswerKey] = 6 只对 AnswerKey 列施加筛选器,无法影响整个 Answers 表”, Answers[AnswerKey]是 Answers表的一部分, 为什么无法影响整个 Answers 表,还有“两个筛选器在各自的原始筛选上下文中计值,计值后的结果求交集得到最终的筛选上下文”,求交集,应该是整个answers表和 Answers[AnswerKey] = 6 的行求交集,结果应该是answers表中 Answers[AnswerKey] = 6的行,answers表的扩展表包含Customers表,应该会影响到啊

成员
powersum

请问老师:已为两个数据表建立了多对一的关系,在数据透视表的字段列表中中也能看到两张表的所有字段,是否可以将副表的字段放入行筛选区域,而且能实现筛选功能的那种一一对应的效果。
现在将副表的某字段放在行筛选区域上,出现了显示此字段所有值的情况,而和初始存在于行筛选区域中主表的字段没有关系。这样就失去了筛选功能了。
当然用度量值是可以实现的,但是直接放在行筛选区域是否可以呢?

成员
windpursuer

高老师,测试第2题有疑问。我颅内的画面:
VALUES ( Product[Color] )的值形成矩阵的行标签,作为CAL中第2个参数的原始上下文,那么filter中第1个参数sales表应该是被先被颜色筛选后再被 [quantity]>3 筛选,理应返回正确结果啊?
我想象的是如果filter中第一参数换成ALL(sales),才能抵消前面VALUES形成的筛选效果,得到图片的结果?

成员
windpursuer

耳目一新

成员
150

高老师您好,关于筛选生效的触发有点迷惑,上面提到:
“日期表的扩展表只包含日期表本身,即使日期可以被 Sales、Product、Product Subcategory 和 Product Category 表筛选。因为使筛选生效的机制是双向筛选而非扩展表。”
下面一段又说:“正如你所看到的,这条规则与我们之前的表述相同,而当时我们还在谈论表和关系。实际上筛选上下文并不真正通过关系“传播”,它将筛选效果应用于包含该列的所有表,因为它基于扩展表生效。”
那到底筛选的机制是基于表关系,还是基于扩展表呢? 如何理解上面的这2段话呢?

DAX 圣经

导读

初识 DAX

DAX 基础知识

DAX 原理

DAX 高级原理

基础函数类型

迭代函数

CALCULATE 函数

CALCULATE 调节器

基础表函数

条件判断函数

查找匹配函数

时间智能函数

统计类函数

投影函数

分组/连接函数

集合函数

其他函数