理解ALL类函数

本文完整阐述 DAX 中所有 ALL 函数的行为，ALL 函数有两种截然不同的作用，返回表和移除筛选器。你可能习惯用它们返回表，但是当用作 CALCULATE 筛选参数的时候，这些 ALL 函数会执行另一种操作

CALCULATE Modifier 的名称沿革

欢迎来到 CALCULATE Modifier 章节，这个词我译作 CALCULATE 调节器，《DAX 权威指南》第一版虽然阐述了 CALCULATE 调节器的作用，但并没有给这类函数一个正式的名称，CALCULATE Modifier 首次出现大概在两年前 SQLBI 的博文中，随着《DAX 权威指南》第二版的出版，这个新的名词也被正式确定下来。

除了布尔条件和表筛选，CALCULATE 还接受 ALL、ALLSELECT、KEEPFILTERS、USERELATIONSHIP 等函数作为筛选器参数，它们不像前两种筛选器那样直接引入新的筛选上下文，而是会改变新筛选上下文生成的方式，比如 KEEPFILTERS 改变当前筛选器与原始筛选上下文合并的方式，CALCULATE 调节器是一类常用且重要的函数

认识 ALL 类函数的两种用法

ALL 系列包括以下函数：ALL、ALLEXCEPT、ALLNOBLANKROW、ALLCROSSFILTERED 和 ALLSELECTED。它们都可以用作表函数或 CALCULATE 调节器。当用作表函数时，它们的行为更容易理解。 而一旦作为 CALCULATE 调节器，则可能会产生意想不到的结果，因为它们实际上起到移除筛选的效果。

1. -------- 表函数：返回列的不重复值，或表的所有值 --------
   
2. SUMX (
   
3.     ALL ( Sales ),         // 返回完整的 SALES 表
   
4.     Sales[Quantity] * Sales[Net Price]
   
5. )
   
6. 
   
7. CALCULATE (
   
8.     [Sales Amount],
   
9.     FILTER (
   
10.         ALL ( Sales ),     // 返回完整的 SALES 表  
    
11.         Sales[Quantity] > 0
    
12.     )
    
13. )
    
14. 
    
15. -------- CALCULATE 调节器：移除所有正在生效的筛选器 --------
    
16. CALCULATE ( [Sales Amount], ALL ( Sales ) )  // 移除 SALES 表的扩展表上所有正在生效的筛选器

复制代码

ALL

1. ALL ( [<TableNameOrColumnName>] , [ <ColumnName>, [ <ColumnName>, [ … ] ] ] )

复制代码

返回表中的所有行或列中的所有值，忽略任何筛选器。

• 用作 CALCULATE 调节器时，移除<TableNameOrColumnName>的扩展表中已应用的任何筛选器。
• 用作表函数时，ALL 是一个简单的函数。ALL(<ColumnName>)返回一列或多列的所有不重复值；ALL(<TableName>)返回表的所有行。
  

用作 CALCULATE 调节器时，它起到 REMOVEFILTERS 函数的作用：如果列存在筛选器，ALL 会移除这个筛选器。ALL 遵循扩展表，所以上图中第三个公式中的ALL(Sales)从模型中移除所有筛选器：因为 Sales 表的扩展表包括整个模型。另外，不带参数的 ALL 从整个模型中移除任何筛选器。


如果对列进行交叉筛选，ALL不会删除筛选器。只有直接筛选器才会被删除。因此，如果 Product 表的另一列上有筛选器，使用 ALL(Product[Color])作为 CALCULATE 调节器可能仍然会使 Product[Color]被交叉筛选。

包含 ALL 函数的 DAX 表达式的正常行为是忽略所应用的全部筛选器。 但遇到「自动匹配」(auto-exist) 时，这种忽略不会发生。「自动匹配」是一种用于优化筛选的技术，可以减少某些 DAX 查询所需的处理量，你可以在 DAX 高级原理章节了解关于它的详细内容。

ALLEXCEPT

1. ALLEXCEPT ( <TableName>, <ColumnName>, [ <ColumnName>, [ … ] ] )

复制代码

返回表中受指定列筛选器影响的行以外的所有行，当用作 CALCULATE 调节器时，移除<TableName>的扩展表中已应用的任何筛选器，只保留<ColumnName>的筛选条件。


用作表函数时，ALLEXCEPT 移除参数指定的列，返回表中剩余列的所有不同值。如果用作调节器，ALLEXCEPT 考虑完整的扩展表，此时ALLEXCEPT 与 ALL 类似，但它保留参数列中的筛选器。

1. --------- 移除 Customer 表所有列上的筛选器，只保留对城市的筛选 --------
   
2. ALLEXCEPT ( Customer, Customer[City] )
   
3. 
   
4. --------- 从 Sales 表的扩展表上移除所有列筛选器，只保留对 Date 表和城市列的筛选 --------
   
5. ALLEXCEPT ( Sales, 'Date', Customer[City] )

复制代码

ALL 和 VALUES 的组合可以实现 ALLEXCEPT 的效果，但两者仍然有所区别，ALLEXCEPT 只移除筛选器，而 ALL 移除筛选器，然后 VALUES 通过施加新筛选器保留交叉筛选。这种差异虽然很微妙，但很重要。

ALLNOBLANKROW

1. ALLNOBLANKROW ( <TableNameOrColumnName> [<ColumnName>, [ <ColumnName>, [ … ] ]  )

复制代码

返回表中除空白行以外的所有行，或列中的所有值，移除可能已应用的任何筛选器


用作表函数时，ALLNOBLANKROW 的行为与 ALL 类似，但不会返回由于无效关系而可能添加的空行。如果表中存在空白，ALLNOBLANKROW 仍然可以返回空行。唯一不返回的行是引擎为了修复无效关系自动添加的行。用作 CALCULATE 调节器时，ALLNOBLANKROW 将所有筛选器替换为仅删除空白行的新筛选器。因此，作为参数的所有列或表将只过滤掉空值。

1. ALLNOBLANKROW ( Customer )
   
2. 
   
3. ALLNOBLANKROW ( Customer[Country], Customer[State] , Customer[City] )

复制代码

ALLSELECTED

1. ALLSELECTED ( [<TableNameOrColumnName>], [ <ColumnName>, [ <ColumnName>, [ … ] ] ] )

复制代码

用作表函数时，ALLSELECTED 返回表(或列)在最后一个影子筛选上下文中的筛选结果。用作调节器时，它恢复每列的最后一个影子筛选上下文。如果在不同的影子筛选上下文中存在多列，ALLSELECTED 使用每个列的最后一个影子筛选上下文。

ALLSELECTED 是 DAX 中最复杂的函数，将在专门的文章中介绍

ALLCROSSFILTERED

1. ALLCROSSFILTERED ( <TableName> )

复制代码

ALLCROSSFILTERED 只能用作 CALCULATE 调节器，不能用作表函数，且ALLCROSSFILTERED 只接受表参数。ALLCROSSFILTERED 移除扩展表（与 ALL 相同）上的所有由交叉筛选产生的筛选器，包括通过双向筛选直接或间接访问扩展表的表或列的筛选器。

函数功能小结

函数	表函数	CALCULATE 调节器
ALL	返回列或表的所有不重复值	从列或扩展表中移除任何筛选器。ALL 从不添加筛选器，只移除参数列出现的所有筛选器
ALLEXCEPT	返回表中所有不重复值，移除扩展表参数列上的筛选器	从扩展表中移除筛选器，保留来自后续参数的列（或表）中的筛选条件
ALLNOBLANKROW	返回列或表的所有不重复值，忽略为无效关系添加的空白行	从列或扩展表中移除任何筛选器；并添加一个只删除空行的筛选器。因此，即使公式中没有筛选器，它也会主动添加一个到上下文中
ALLSELECTED	从最后一个影子筛选上下文中返回列或表的不重复值	如果存在影子筛选上下文，ALLSELECTED 还原表或列上的最后一个影子上下文，否则不执行任何操作。ALLSELECTED 始终添加筛选器，即使在显示所有值的情况下也是如此
ALLCROSSFILTERED	不能作为表函数	从扩展表中删除任何筛选器，包括可以通过双向筛选直接或间接访问的表。ALLCROSSFILTERED 从不添加筛选器，只删除当前正在生效的筛选器

表函数下的 ALL, ALLEXCEPT 和 ALLNOBLANKROW

ALL 是一个常用的函数，它返回表的所有行或列的所有值，具体取决于使用的参数。例如，以下 DAX 查询返回产品表中的所有行：

1. EVALUATE
   
2. ALL ( Product )

复制代码

不能在 ALL 的参数中使用返回表的表达式。你必须使用表名或列名。如果使用单个列, 则结果是一列包含其唯一值的表, 如图所示。

1. EVALUATE
   
2. ALL ( Product[Class] )

复制代码

查询列返回所有唯一值的列表


可以在 ALL 函数的参数中指定同一表中的更多列。如果使用多列，那么结果将是具有相同列数的表，包含了这些列中现有值的唯一组合。例如，下面的表达式产生图 1 所示的结果。

1. EVALUATE
   
2. ALL ( Product[Class], Product[Color] )
   
3. ORDER BY Product[Color]

复制代码

图 1 对多列所有值的查询仅返回现有值的唯一组合列表


ALL 忽略任何现有筛选器。你可以将 ALL 用作迭代函数的参数, 如 SUMX 和 FILTER ，或者作为 CALCULATE 函数的筛选器参数。


如果希望用 ALL 函数调用表的多数列，那么可以使用 ALLEXCEPT 替代。ALLEXCEPT 的语法需要一个表，后跟要从结果中排除的列。因此，ALLEXCEPT 返回一个表，包含了表中其他列现有值组合的唯一列表。


实际上，ALLEXCEPT 是一种自动包含 ALL 的 DAX 表达式写法，包含了未来可能出现在表中的任何其他列。例如，如果有一个包含五列的产品表(ProductKey, Product Name, Brand, Class, Color)，下面的语法会产生相同的结果:

1. ALL ( Product[Product Name], Product[Brand], Product[Class] )
   
2. 
   
3. ALLEXCEPT ( Product, Product[ProductKey], Product[Color] )

复制代码

但是, 如果以后产品表新增两列 Product[Unit Cost] 和 Product[Unit Price], 那么 ALL 将忽略它们, 而之前的 ALLEXCEPT 将返回等效的：

1. ALL (
   
2.     Product[Product Name],
   
3.     Product[Brand],
   
4.     Product[Class],
   
5.     Product[Unit Cost],
   
6.     Product[Unit Price]
   
7. )

复制代码

下面的查询返回产品表中除产品代码和颜色之外的所有列。图中的结果与原始表的行数相同，因为结果包含 ProductKey 列，其每一行的值都是唯一的。如果换作其他列组合可能会返回更少的行数，因为 ALLEXCEPT 会删除返回列中的重复值组合。

1. EVALUATE
   
2. ALLEXCEPT ( Product, Product[ProductKey], Product[Color] )

复制代码

ALLEXCEPT 返回参数中未指定的所有列的现有值组合


在前面的示例中，你已经在 EVALUATE 语句中看到了 ALL 的用法，该语句在没有任何现有筛选器的情况下执行 DAX 表达式。出于这个原因，最好再展示一个使用度量值的示例，该度量值计算透视表环境下 ALL 返回的行数，其中每个单元格的计值环境都不同。考虑以下度量值：

1. [Products]:= COUNTROWS ( Product )
   
2. [All Products]:= COUNTROWS ( ALL ( Product ) )
   
3. [All Brands]:= COUNTROWS ( ALL ( Product[Brand] ) )

复制代码

你可以在下图看到每个度量值的不同结果




All Products 和 All Brands 两个度量值忽略位于行标签的类别筛选，总是返回相同的结果


对于每个产品类别，All Products 和 All Colors 中都有各自相同的数字。ALL 语句的计算忽略了透视表为每个单元格定义的筛选器。


当你在关系的父表上调用 ALL 时，如果子表包含一个或多个与父表中的任何值均不匹配的行，那么你将检索到额外的空白行。通过使用 ALLNOBLANKROW 而不是 ALL，可以从结果中省略这一行。


考虑以下度量值：

1. [All Products]:= COUNTROWS ( ALL ( Product ) )
   
2. [All NoBlank Products]:= COUNTROWS ( ALLNOBLANKROW ( Product) )
   
3. [All Brands]:= COUNTROWS ( ALL ( Product[Brand] ) )
   
4. [All NoBlank Brands]:= COUNTROWS ( ALLNOBLANKROW ( Product[Brand] ) )
   
5. [All Sizes]:= COUNTROWS ( ALL ( Product[Size] ) )
   
6. [All NoBlank Sizes]:= COUNTROWS ( ALLNOBLANKROW ( Product[Size] ) )

复制代码

在图 2 中，你可以看到 ALL 和 ALLNOBLANKROW 度量值之间的区别。应用于产品表和 Products[Model]列的 ALL 版本都比 ALLNOBLANKROW 版本多返回一行。原因是销售表中的某些行在产品表中没有匹配的行，因此这实际上向产品表中添加了额外的行，你可以在图 2 中的(空白)行中看到结果。




图 2 如果目标表包含为不匹配值附加的空白行，All 和 AllNoBlank 的结果将不同


请注意，和 Size 相关的 ALL 和 ALLNOBLANKROW 返回相同的结果。它们查询的是 Products[Size]列的值，在这种情况下, ALL 和 ALLNOBLANKROW 函数返回相同值的原因是 Products[Size]列已包含产品的空白值。在图 3 中的示例中, 有 569 个空白尺寸的产品, 外加一个由销售表中不匹配产品带来的额外的空白行，总共为 570 个。所有这些行都被分到同一个 (空白) 值的组中。




图 3 透视表行标签显示了所有产品尺寸，其中第一项空值包含了尺寸为空和由销售表存在不匹配产品产生的空值


当你需要用 DAX 公式忽略关系中未匹配的值时，才应该使用 ALLNOBLANKROW。通常，对 ALL 的使用很普遍，而 ALLNOBLANKROW 则很少被使用。

CALCULATE 调节器

ALL 用作 CALCULATE 调节器时，只删除筛选器，不返回表。此时，它的作用与REMOVEFILTERS 相同。这两种行为（移除筛选器和返回完整的表）看似效果相同，实际有显著区别。

1. PercOfProductsSold =
   
2. DIVIDE (
   
3.     CALCULATE (
   
4.         [NumOfProducts],       -- Number of products  
   
5.         Sales                  -- 被当前筛选上下中的 Sales 表 筛选
   
6.     ),      
   
7.     CALCULATE (
   
8.         [NumOfProducts],       -- Number of products
   
9.         ALL ( Sales )          -- 移除来自 Sales 表 的所有筛选
   
10.     )  
    
11. )

复制代码

使用 ALL(Sales)并不意味着“使用 Sales 中的所有行进行筛选”。它的意思是“从 Sales 表的扩展表中删除所有筛选器”。也就是说上面的公式中如果没有应用任何筛选器，分母的产品数就是产品的总数。


ALL 是一个有两种语义的函数，为了避免由此带来的歧义，你可以使用 REMOVEFILTERS 进行替换，比如上面的公式修改为：

1. PercOfProductsSold =
   
2. DIVIDE (
   
3.     CALCULATE (
   
4.         [NumOfProducts],            
   
5.         Sales                     
   
6.     ),      
   
7.     CALCULATE (
   
8.         [NumOfProducts],           
   
9.         REMOVEFILTERS ( Sales )   
   
10.     )  
    
11. )

复制代码

测试题

在分析上面的公式时，我们提到过分母使用 ALL(Sales)并不意味着“使用 Sales 中的所有行进行筛选”，现在请你思考一下，如果我们想在分母中使用 Sales 中的所有行进行筛选，需要如何改写公式？

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

答案解析

在 ALL ( Sales )外部使用 CALCULATETABLE 改变了 ALL 函数的语义，从调节器改为返回表的函数，原因是 ALL 作为 CALCULATE 第一参数的时候返回表，FILTER 写法的原理也是如此。


值得反复强调的是，所有用作筛选参数的表实际上都是扩展表。因此，删除筛选器的操作不仅会影响基础表，还会影响整个扩展表。比如 ALL (Sales)在 Sales 的扩展版本上执行 REMOVEFILTERS，从表和所有相关维度中删除筛选器。

看起来不错

LZ帖子不给力，勉强给回复下吧

楼下的接上

不错 支持下

无论是不是沙发都得回复下

看起来好像不错的样子

啥玩应呀

确实不错，顶先

珍爱生命，果断回帖。