08.1 理解ALL函数

理解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、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 ( [] , [ , [ , [ … ] ] ] )返回表中的所有行或列中的所有值，忽略任何筛选器。当用作CALCULATE调节器时，移除的扩展表中已应用的任何筛选器。当用作表函数时，ALL是一个简单的函数。使用列参数时，它返回一列或多列的所有不重复值；使用表作为参数时，返回表的所有行。用作CALCULATE调节器时，它起到REMOVEFILTER函数的作用：如果列存在筛选器，ALL会移除这个筛选器。ALL遵循扩展表，所以CALCULATE调节器公式的ALL(Sales)从模型中移除所有筛选器：因为Sales表的扩展表包括整个模型的所有表。另外，不带参数的ALL从整个模型中移除任何筛选器。如果对列进行交叉筛选，ALL不会删除筛选器。只有直接筛选器才会被全部删除。因此，如果Product表的另一列上有筛选器，使用ALL(Product[Color])作为CALCULATE调节器可能仍然会使Product[Color]被交叉筛选。

ALLEXCEPT
ALLEXCEPT ( , , [ , [ … ] ] )返回表中受指定列筛选器影响的行以外的所有行，当用作CALCULATE调节器时，移除的扩展表中已应用的任何筛选器，只保留的筛选条件。用作表函数时，ALLEXCEPT移除参数指定的列，返回表中剩余列的所有不同值。如果用作调节器，ALLEXCEPT考虑完整的扩展表，此时ALLEXCEPT与ALL类似，但它保留参数列中的筛选器。————- 移除Customer表所有列上的筛选器，只保留对城市的筛选 ————ALLEXCEPT(Customer, Customer[City])————- 从Sales表的扩展表上移除所有列筛选器，只保留对Date表和城市列的筛选 ————ALLEXCEPT(Sales,’Date’, Customer[City]) ALL和VALUES的组合可以实现ALLEXCEPT的效果，但两者仍然有所区别，ALLEXCEPT只移除筛选器，而ALL移除筛选器，然后VALUES通过施加新筛选器保留交叉筛选。这种差异虽然很微妙，但很重要。

ALLNOBLANKROW
ALLNOBLANKROW ( [, [ , [ … ] ] )返回表中除空白行以外的所有行，或列中的所有值，移除可能已应用的任何筛选器用作表函数时，ALLNOBLANKROW的行为与ALL类似，但不会返回由于无效关系而可能添加的空行。如果表中存在空白，ALLNOBLANKROW仍然可以返回空行。唯一不返回的行是引擎自动添加的行，以修复无效关系。用作CALCULATE调节器时，ALLNOBLANKROW将所有筛选器替换为仅删除空白行的新筛选器。因此，所有列将只过滤掉空值。ALLNOBLANKROW(Customer)ALLNOBLANKROW(Customer[Country], Customer[State], Customer[City])### ALLSELECTED ALLSELECTED ( [], [ , [ , [ … ] ] ] )用作表函数时，ALLSELECTED返回表(或列)在最后一个影子筛选上下文中的筛选结果。用作调节器时，它恢复每列的最后一个影子筛选上下文。如果在不同的影子筛选上下文中存在多列，ALLSELECTED使用每个列的最后一个影子筛选上下文。 ALLSELECTED是DAX中最复杂的函数，将在专门的文章中介绍

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

## 表函数下的ALL, ALLEXCEPT和ALLNOBLANKROW ALL是一个常用的函数，它返回表的所有行或列的所有值，具体取决于使用的参数。例如，以下 DAX 查询返回产品表中的所有行：EVALUATEALL(Product)不能在ALL的参数中使用返回表的表达式。你必须使用表名或列名。如果使用单个列, 则结果是一列包含其唯一值的表, 如图所示。EVALUATEALL(Product[Class])查询列返回所有唯一值的列表可以在ALL函数的参数中指定同一表中的更多列。如果使用多列，那么结果将是具有相同列数的表，包含了这些列中现有值的唯一组合。例如，下面的表达式产生图1所示的结果。EVALUATEALL(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])下面的查询返回产品表中除产品代码和颜色之外的所有列。图中的结果与原始表的行数相同，因为结果包含ProductKey列，其每一行的值都是唯一的。如果换作其他列组合可能会返回更少的行数，因为ALLEXCEPT会删除返回列中的重复值组合。EVALUATEALLEXCEPT(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调节器时，只删除筛选器，不返回表。此时，它的作用与REMOVEFILTER相同。这两种行为（移除筛选器和返回完整的表）看似效果相同，实际有显著区别。PercOfProductsSold =DIVIDE(CALCULATE([NumOfProducts], — Number of productsSales — 被当前筛选上下中的 Sales表筛选),CALCULATE([NumOfProducts], — Number of productsALL(Sales))— 被 Sales表的所有记录筛选))使用ALL(Sales)并不意味着“使用Sales中的所有行进行筛选”。它的意思是“从Sales表的扩展表中删除所有筛选器”。也就是说上面的公式中如果没有应用任何筛选器，分母的产品数就是产品的总数。ALL是一个有两种语义的函数，为了避免由此带来的歧义，你可以使用REMOVEFILTER进行替换，比如上面的公式修改为：PercOfProductsSold =DIVIDE(CALCULATE([NumOfProducts],Sales),CALCULATE([NumOfProducts],REMOVEFILTER(Sales))))### 测试题在分析上面的公式时，我们提到过分母使用ALL(Sales)并不意味着“使用Sales中的所有行进行筛选”，现在请你思考一下，如果我们想在分母中使用Sales中的所有行进行筛选，需要如何改写公式？ CALCULATETABLE写法PercOfProductsSold =DIVIDE(CALCULATE([NumOfProducts],Sales),CALCULATE([NumOfProducts],CALCULATETABLE(ALL(Sales)))) FILTER写法PercOfProductsSold =DIVIDE(CALCULATE([NumOfProducts],Sales),CALCULATE([NumOfProducts],FILTER(ALL(Sales),1)))

答案解析

在ALL ( Sales )外部使用CALCULATETABLE改变了ALL函数的语义，从调节器改为返回表的函数，原因是ALL作为CALCULATE第一参数的时候返回表，FILTER写法的原理也是如此。值得反复强调的是，所有用作筛选参数的表实际上都是扩展表。因此，删除筛选器的操作不仅会影响基础表，还会影响整个扩展表。比如ALL (Sales)在Sales的扩展版本上执行REMOVEFILTER，从表和所有相关维度中删除筛选器。