理解扩展表 Expanded Tables

扩展表（Expanded Tables）理论是 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）：是沿着关系添加到扩展表的列。
  

列筛选器的一般规则

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

1. [RedSales] :=
   
2. CALCULATE (
   
3.     SUM ( Sales[Quantity] ),
   
4.     Product[Color] = "Red"
   
5. )
   

复制代码

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




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


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

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

表作为筛选器

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


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

1. [NumOfCategories] :=
   
2. COUNTROWS ( 'Product Category')
   
3. 
   
4. [NumOfCategoriesFilteredByProduct] :=
   
5. CALCULATE ( COUNTROWS ( 'Product Category' ), Product )
   

复制代码

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




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


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


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

1. [NumOfCategoriesFilteredByColor] :=
   
2. CALCULATE (
   
3.     COUNTROWS ( 'Product Category' ),
   
4.     FILTER (
   
5.         ALL ( Product[Color] ),
   
6.         Product[Color] = "Green"
   
7.     )
   
8. )
   
9. 
   
10. [NumOfCategoriesFilteredByProduct] :=
    
11. CALCULATE (
    
12.     COUNTROWS ( 'Product Category' ),
    
13.     FILTER (
    
14.         ALL ( Product ),
    
15.         Product[Color] = "Green"
    
16.     )
    
17. )
    

复制代码

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




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


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

RELATED, RELATEDTABLE 和扩展表

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

变量和扩展表

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

1. DEFINE
   
2.     VAR SalesA =
   
3.         CALCULATETABLE ( Sales, USERELATIONSHIP ( Sales[Date], 'Date'[Date] ) )
   
4.     VAR SalesB =
   
5.         CALCULATETABLE ( Sales, USERELATIONSHIP ( Sales[DueDate], 'Date'[Date] ) )
   
6. EVALUATE
   
7. 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 中使用扩展表，例如，下面这个度量值可以正常计值：

1. [SalesOfSameColorAndCategory] :=
   
2. CALCULATE (
   
3.     SUMX (
   
4.         Sales,
   
5.         Sales[Quantity] * Sales[UnitPrice]
   
6.     ),
   
7.     ALLEXCEPT (
   
8.         Product,
   
9.         Product[Color],
   
10.         'Product Category'[Category]
    
11.     )
    
12. )
    

复制代码

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

1. ALLEXCEPT ( Product, 'Product Category' )
   
2. ALLEXCEPT ( Product,
   
3.            'Product Category'[ProductCategoryKey], 'Product Category'[Category] )

复制代码

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

CALCULATE 调节器和扩展表

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

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

复制代码

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

扩展表的性能提示

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


另外，通过阅读本文，你不难发现一种新的筛选器用法：直接将表作为 CALCULATE 的筛选器参数，比如上文中的[NumberOfSales]公式。这种写法简单粗暴，事实证明在某些情况下也确实非常有效，但我仍然建议你谨慎使用，原因是：用表作为筛选器，尤其是数据量较大的事实表或者复杂的模型中，会遇到明显的性能问题，也就是公式的计值时间显著增加，事实表越大，耗时越显著。这种现象表明，虽然扩展表原理很有用，但将事实表直接作为筛选器参数是一种低效的筛选器定义方式。


出于性能考虑，在生产环境中我建议你优先选择 CROSSFILTER 函数激活双向筛选，其次考虑直接修改关系类型为双向筛选，尽量不要使用大数据量的事实表作为筛选器。仅对数据量较小的表或出于测试目的的情况下使用。

扩展表理解测试

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

第一题

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




第一题的示例数据和公式


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

游客，如果您要查看本帖隐藏内容请回复

        
   

第二题

下面这个查询计算数量大于 3 的销售记录的平均销售额，然后按颜色切片：

1. DEFINE
   
2.     MEASURE Sales[Average Sales Amount] =
   
3.         AVERAGEX ( Sales, 'Sales'[Quantity] * 'Sales'[Net Price] )
   
4. EVALUATE
   
5. ADDCOLUMNS (
   
6.     VALUES ( Product[Color] ),
   
7.     "Sales", CALCULATE(
   
8.         [Average Sales Amount],
   
9.         FILTER ( Sales, Sales[Quantity] > 3 )
   
10.     )
    
11. )

复制代码

查询并没有计算出正确结果，每行返回了相同的值





错误原因是什么？应该如何纠正？

游客，如果您要查看本帖隐藏内容请回复

第三题


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

1. CALCULATE (
   
2.       DISTINCTCOUNT ( Customers[CustomerKey] ),
   
3.       Answers,
   
4.       Answers[AnswerKey] = 6
   
5. )

复制代码

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

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

游客，如果您要查看本帖隐藏内容请回复

这套视频很不错！！！！

大人，此事必有蹊跷！

鼎力支持！！

站位支持

这个视频很不错，推荐一下

努力学习中

大人，此事必有蹊跷！

回个帖子，下班咯~

佩服佩服！