理解ALL函数 - DAX - Power BI圣经

本文完整阐述 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 调节器时，移除<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

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 相同）上的所有由交叉筛选产生的筛选器，包括通过双向筛选直接或间接访问扩展表的表或列的筛选器。

函数功能小结

本文隐藏内容查看价格为3G币，请先

单独购买的内容长期有效，不受时间限制（购买前先刷新当前页面）。加入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 用作迭代函数的参数, 如 SUMX 和 FILTER ，或者作为 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]
)

下面的查询返回产品表中除产品代码和颜色之外的所有列。图中的结果与原始表的行数相同，因为结果包含 Product Name 列，其每一行的值都是唯一的。如果换作其他列组合可能会返回更少的行数，因为 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 Brands 中都有各自相同的数字。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[Brand]列的 ALL 版本都比 ALLNOBLANKROW 版本多返回一行。原因是销售表中的某些行在产品表中没有匹配的行，因此这实际上向产品表中添加了额外的行，你可以在图 2 中的(空白)行中看到结果。

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

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

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

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

如果想让图 3 中由参照完整性产生的空行参与计数，可以使用COUNTROWS(VALUES('Products'))

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极客”官方公众号。

答案解析

本文隐藏内容查看价格为3G币，请先

单独购买的内容长期有效，不受时间限制（购买前先刷新当前页面）。加入VIP会员可享受全站权益，性价比更高。

109

说点什么

1000

订阅本文评论

最新最旧得票最多

成员

chenchen

高老师，请教一下，
以下度量值：
销售：ALL 订单利润率大于12% = CALCULATE(
[销售：销售额合计],
FILTER(‘订单’,
‘订单'[利润率]>0.12
),
ALL(‘地区’)
)。结果：筛选单笔订单利润率大于0.12的订单，并求销售额合计

‘地区’表位于‘订单’表的一端，以上度量值，ALL应该是作为调节器，移掉’地区’表的筛选的。可是我发现，我使用’地区’表里的任何字段去作切片器时，还是能对这个度量值的结果产生影响。就是ALL并没有移除外部筛选，这是为什么？

jb870610

高老师，图1红框中的细节，对应CALCULATE指南中的哪一步啊？CALCULATE指南里面好像没有深入展开，特别说明这一细节吧？

高老师，图1红框中的Products，是这个度量值吗？[Products]:= COUNTROWS ( Product )，如果是的话， COUNTROWS统计的是一端表的行数，（blank）返回的是不是应该是569，而不是570啊？

高老师，图1中，应该返回表中的所有行或列中的不重复值吧？

153****1882

高老师您好，文中提到“包含 ALL 函数的 DAX 表达式的正常行为是忽略所应用的全部筛选器。但遇到「自动匹配」(auto-exist) 时，这种忽略不会发生。”怎么理解？自动匹配是自动保留可能存在的关系，跟all函数怎么结合理解，能不能举个例子或者再深入解释一下？谢谢。

高老师，文章最后一句话—–”值得反复强调的是，所有用作筛选参数的表实际上都是扩展表。因此，删除筛选器的操作不仅会影响基础表，还会影响整个扩展表。比如 ALL (Sales)在 Sales 的扩展版本上执行 REMOVEFILTERS，从表和所有相关维度中删除筛选器。“—-适用于图1中的公式（可以用这句话来解释图1中的公式的结果），还是适用于图2中的公式（可以用这句话来解释图2中公式的结果），还是都适用啊？

高老师，红框里面的All Colors 在上文和图中并没有出现，应该是All Brands吧？

高老师，红框1，ALL作为表函数，应该只能返回列的所有不重复值，不能返回表的所有不重复值，只能返回表的所有行吧？红框2，ALLEXCEPT作为表函数，移除扩展表参数列上的筛选器，这个感觉是作为调节器的作用吧，跟前面的论述是不是互相矛盾了，前面说—–用作表函数时，ALLEXCEPT 移除参数指定的列，返回表中剩余列的所有不同值。如果用作调节器，ALLEXCEPT 考虑完整的扩展表，此时ALLEXCEPT 与 ALL 类似，但它保留参数列中的筛选器。

高老师，红框3中的公式为什么不用方框2中的描术来解释？是不适用吗？

高老师，第1个红框里面表述的是什么意思啊？理解不清楚呢，跟第2个红框里面是不是说的同一回事啊？

你猜

老师，all（’表’）和all(‘表'[全部字段])的区别是不是有两点？
第一：作为表函数，all（’表’)包含表中的所有数据，all(‘表'[全部字段])包含非重复的数据。
第二：作为调节器函数，all（’表’)可以移除扩展表筛选器，而all(‘表'[全部字段])无法移除扩展表筛选器。
例如下表：
all(‘人’）包含作为表函数，可以获取表中所有数据，而all(‘人'[name],’人'[num]) 只可获得非重复行，记录｛李四，2｝有两行，只保留一行。
all(‘人’）可以移除掉表’人’和表’组’中的所有筛选器，而all(‘人'[name],’人'[num]) ，只能移除’人'[name],’人'[num])的筛选器。

之前关于该条的提问发现好像是不公开的，也没找到地方可以重新设置，就重新再提问一下。
高老师，在讲allexcept作为表函数时，EVALUATE
ALLEXCEPT ( Product, Product[ProductKey], Product[Color] )，结果应该是不包括productkey的（得到的结果应该是有可能会删除重复行），文中讲结果包含productkey是什么意思？

Screenshot_20230304_090833_com.baidu.searchbox_edit_515223863471903.jpg

chen1997

老师看了上面的内容还是有些不太理解，向你请教一下，下面有个例子，有两个表一个父表，一个子表，如图所示，构建两个度量值 all行数父=countrows（all（父表）），allnoblankrow行数父=countrows（allnoblankrow（父表）），把子表的姓名放在筛选器上，结果如图，因为all跟allnoblankrow在这里当作表函数，忽略外部筛选，返回完整表为什么计数的时候all是5，父表不是只有4行吗？其中的原理是什么

Clarlechen

修改后 DAX
最佳类目 =
var zuijia=all(‘Product'[Category],’Product'[Subcategory])
var pinjun=
averagex(
zuijia,
sumx(
RELATEDTABLE(Sales),
‘Sales'[Quantity]*’Sales'[Net Price])
)
var zuihao=
filter(zuijia,
sumx(
RELATEDTABLE(Sales),
‘Sales'[Quantity]*’Sales'[Net Price])>=pinjun*2)
return zuihao

修改前DAXBestCategories =
var subCategories=all(‘Product'[Category],’Product'[Subcategory])//完整的类目，子类的集合
var averagesales=
averagex(
subCategories,
sumx(
RELATEDTABLE(‘Sales’),//一对多的去链接销售表，算出每个组合的平均销售额
‘Sales'[Quantity]*’Sales'[Net Price]
)
)

var topCategories=
filter(
subCategories,
var salesofcaterory=
sumx(
RELATEDTABLE(sales),
‘Sales'[Net Price]*’Sales'[Quantity]
)
return salesofcaterory>=averagesales*2
)
return topCategories

老师，修改前DAX是您在DAX圣经书籍第三章all函数介绍写的，后面我修改了下DAX的表达式，输出内容是对的但是类别和子类的表格相反

您输出的顺序是类别和子类别
我的输出的顺序是子类别和类别

麻烦告知下为何出现这个问题哈

做一名学霸

关于测试题：
PercOfProductsSold =
DIVIDE (
CALCULATE (
[NumOfProducts], — Number of products
Sales — 被当前筛选上下中的 Sales 表筛选
),
CALCULATE (
[NumOfProducts], — Number of products
ALL ( Sales ) — 移除来自 Sales 表的所有筛选
)
)

老师好，我直接把分母的ALL ( Sales ) 修改成Sales，也应该能够使用 Sales 中的所有行进行筛选吧？

老师好，为啥我写的没有过滤空白行啊？
是否这样理解：
1.作为表函数时，返回一张表，但会保留空白行、空值并去重；
2.作为调节器时，只保留一个删除空值的筛选器。如果参数是表，那就用来删除表中的空白行（不是任何空白值，比如有的同一行中既有空白，也有非空白，这一行不就不会被过滤掉）；如果参数是一列，那就会用来过滤该列中的空白值；如果参数是多列，那就用来过滤各个列中相同行都是空白的行（比如，A列的第1行和B列的第一行都是空，这一行就会被过滤；A列的第一行是空、B列的第一行非空，这一行就不会被过滤）？

老师好，请问：
1.什么叫交叉筛选？
2.什么叫直接筛选器？
3.比如，CALCULATE（SUM（Sales），FITER(ALL（Product[Color]）,Product[Color]=”BLACK”））
ALL清除了Product[Color]列的筛选器，但为啥不清除Product[Color]=”BLACK”这个筛选器？是因为这属于交叉筛选吗？
直接筛选是透视表、报表中的行标签、列标签、切片器等外部筛选器以及ALL函数内部的筛选器吗？

小本本

为什么上面的是622和下面的575差的还挺多, 为啥呢?

影月流伤

老师，我又遇到问题了
role_id和login_time都是账号信息的列，然后我建了一个度量值
计数 = COUNTROWS(CALCULATETABLE(‘账号信息’,ALLEXCEPT(‘账号信息’,’账号信息'[login_time])))
拖到卡片图里面，再新建了两个切片器，一个用login_time，一个用role_id
按理说卡片图上的值会只根据 login_time变化，因为屏蔽了其他筛选器列。
可是实际中也会根据role_id变化，这是何解！

游客

老是，我有一个度量值 COUNTROWS(ALLEXCEPT( Customer, Customer[City] ))
然后有一个切片器对Customer[City] 的筛选，可是不管怎么筛选得到的结果都是Customer的总行数，这个怎么回事呀，不是保留了对Customer[City]列的筛选吗

理解ALL类函数

CALCULATE Modifier 的名称沿革

认识 ALL 类函数的两种用法

ALL

ALLEXCEPT

ALLNOBLANKROW

ALLSELECTED

ALLCROSSFILTERED

函数功能小结

表函数下的 ALL, ALLEXCEPT 和 ALLNOBLANKROW

CALCULATE 调节器

测试题

答案解析

相关推荐

109

说点什么

导读

初识 DAX

DAX 基础知识

DAX 原理

DAX 高级原理

基础函数类型

迭代函数

CALCULATE 函数

CALCULATE 调节器

基础表函数

条件判断函数

查找匹配函数

时间智能函数

统计类函数

投影函数

分组/连接函数

集合函数

其他函数

回顶部

CALCULATE Modifier 的名称沿革

认识 ALL 类函数的两种用法

ALL

ALLEXCEPT

ALLNOBLANKROW

ALLSELECTED

ALLCROSSFILTERED

函数功能小结

表函数下的 ALL, ALLEXCEPT 和 ALLNOBLANKROW

CALCULATE 调节器

测试题

答案解析

相关推荐

109 说点什么

回顶部

109

说点什么