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

理解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 调节器,则可能会产生意想不到的结果,因为它们实际上起到移除筛选的效果。

-------- 表函数:返回列的不重复值,或表的所有值 --------
SUMX (
    ALL ( Sales ),         // 返回完整的 SALES 表
    Sales[Quantity] * Sales[Net Price]
)

CALCULATE (
    [Sales Amount],
    FILTER (
        ALL ( Sales ),     // 返回完整的 SALES 表  
        Sales[Quantity] > 0
    )
)

-------- CALCULATE 调节器:移除所有正在生效的筛选器 --------
CALCULATE ( [Sales Amount], ALL ( Sales ) // 移除 SALES 表的扩展表上所有正在生效的筛选器 )

ALL

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

返回表中的所有行或列中的所有值,忽略任何筛选器。当用作 CALCULATE 调节器时,移除<TableName>的扩展表中已应用的任何筛选器。

当用作表函数时,ALL 是一个简单的函数。使用列参数时,它返回一列或多列的所有不重复值;使用表作为参数时,返回表的所有行。用作 CALCULATE 调节器时,它起到 REMOVEFILTERS 函数的作用:如果列存在筛选器,ALL 会移除这个筛选器。ALL 遵循扩展表,所以 CALCULATE 调节器公式的ALL(Sales)从模型中移除所有筛选器:因为 Sales 表的扩展表包括整个模型的所有表。另外,不带参数的 ALL 从整个模型中移除任何筛选器。

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

ALLEXCEPT

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

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

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

--------- 移除 Customer 表所有列上的筛选器,只保留对城市的筛选 --------
ALLEXCEPT ( Customer, Customer[City] )

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

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

ALLNOBLANKROW

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

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

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

ALLNOBLANKROW ( Customer )
 
ALLNOBLANKROW ( Customer[Country], Customer[State] , Customer[City] )

ALLSELECTED

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

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

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

ALLCROSSFILTERED

ALLCROSSFILTERED ( <TableName> )

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

函数功能小结

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

表函数下的 ALL, ALLEXCEPT 和 ALLNOBLANKROW

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

EVALUATE
ALL ( Product )

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

EVALUATE
ALL ( Product[Class] )

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

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

EVALUATE
ALL ( Product[Class], Product[Color] )
ORDER BY Product[Color]

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

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

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

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

ALL ( Product[Product Name], Product[Brand], Product[Class] )

ALLEXCEPT ( Product, Product[ProductKey], Product[Color] )

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

ALL (
    Product[Product Name],
    Product[Brand],
    Product[Class],
    Product[Unit Cost],
    Product[Unit Price]
)

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

EVALUATE
ALLEXCEPT ( Product, Product[ProductKey], Product[Color] )

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

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

[Products]:= COUNTROWS ( Product )
[All Products]:= COUNTROWS ( ALL ( Product ) )
[All Brands]:= COUNTROWS ( ALL ( Product[Brand] ) )

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

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

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

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

考虑以下度量值:

[All Products]:= COUNTROWS ( ALL ( Product ) )
[All NoBlank Products]:= COUNTROWS ( ALLNOBLANKROW ( Product) )
[All Brands]:= COUNTROWS ( ALL ( Product[Brand] ) )
[All NoBlank Brands]:= COUNTROWS ( ALLNOBLANKROW ( Product[Brand] ) )
[All Sizes]:= COUNTROWS ( ALL ( Product[Size] ) )
[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 相同。这两种行为(移除筛选器和返回完整的表)看似效果相同,实际有显著区别。

PercOfProductsSold =
DIVIDE (
    CALCULATE (
        [NumOfProducts],       -- Number of products  
        Sales                  -- 被当前筛选上下中的 Sales 表 筛选
    ),      
    CALCULATE (
        [NumOfProducts],       -- Number of products
        ALL ( Sales ) )        -- 被 Sales 表 的所有记录筛选
    )  
)

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

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

PercOfProductsSold =
DIVIDE (
    CALCULATE (
        [NumOfProducts],            
        Sales                     
    ),      
    CALCULATE (
        [NumOfProducts],           
        REMOVEFILTERS ( Sales ) )   
    )  
)

测试题

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

公众号二维码加载失败时的替代文字
此处内容已经被作者无情的隐藏,请输入验证码查看内容
验证码:
请关注“PowerBI极客”公众号,回复关键字“ALL”,获取验证码。 【注】手机扫描二维码快速关注“PowerBI极客”官方公众号。

答案解析

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

 

下载面板

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

2
说点什么

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

老师,还是不明白什么是扩展表。所有用作筛选参数的表实际上都是扩展表。用作筛选参数的表?能举个例子吗?