20、理解ALL函数

  本篇文章将介绍ALL函数的所有用法与细节，ALL函数是DAX的核心函数之一，它让我们能够自由地移除筛选器，是一个非常重要的函数，因此我们要彻底掌握ALL函数的每一处细节。

  ALL函数的语法结构

• 语法：

ALL ( <TableName> | [ <ColumnName> , [ <ColumnName>, [ <ColumnName>, [ … ] ] ] )

• 作用：

  ALL函数有两种用法，一种是作为表函数，另一种则是作为筛选调节器，ALL的作用具体如下：

  1、若将ALL用作表函数，将返回参数所指定的表中的所有行，或参数所指定的列的所有值去重后的单列表，忽略任何筛选器；
  2、若将ALL用作筛选调节器，将移除参数所指定的表的扩展表或参数所指定的列上的所有筛选器。

  可能有人会看不懂ALL的语法结构，因此先对ALL能接受的参数做一个简单说明。简单来讲，ALL的参数要么直接是表，要么就是列。若其参数为表，例如：ALL('TableName')，那么这个表只能是基础表而不能是返回表的表函数。若其参数为列，那么可以使用单列或多列，例如：ALL('TableName'[ColumnName1],'TableName'[ColumnName2])，当使用多列时，这些列必须来自同一张表。最后，ALL还能不带任何参数，不带任何参数时只能用作筛选调节器，将移除所有筛选器。

  本篇文章使用到的数据以及数据模型如下图：

  将ALL函数用作表函数

  首先来看下将ALL用作表函数的情况，当ALL被用作表函数时，其作用以及行为都与VALUES非常相像，都是将其参数在数据模型中的可见值以表的形式返回，参数可以是列，也可以是表。但ALL与VALUES还是有所不同的，具体如下：

  1、若参数为列时，ALL将忽略任何对其参数起作用的筛选器，返回一列或多列的所有值去重后的表，所谓的重复值是以行为单位的；
  2、若参数为表时，ALL将忽略任何对其参数起作用的筛选器，返回该表的所有行。其中，作为参数的表只能是基础表，不能是返回表的表达式；
  3、若遇到由参照完整性不匹配而产生的空行，ALL的行为与VALUES的行为一致，即：ALL会返回由参照完整性不匹配而产生的空行。

  下面来看一些帮助理解的例子：

  1、参数为表时，ALL会忽略任何对其参数起作用的筛选器，返回表的所有行，如下图所示：

  2、若参数为单列时，ALL会忽略任何对其参数起作用的筛选器，返回该列的所有值去重后的单列表，如下图所示：

  3、参数为多列时，ALL会忽略任何对其参数起作用的筛选器，返回多列的现有值的唯一值组合，而不是返回的笛卡尔积。简单来说就是从基础表中提取指定的几列，然后以行为单位去重，返回去重后的表。具体如下图所示：

  4、ALL遇到参照完整性不匹配而产生的空行时，会返回该空行，如下图所示：

  这里用到的度量值表达式如下：

商品表的行数 = COUNTROWS('商品表')

ALL_商品表的行数 = COUNTROWS(ALL('商品表'))

values_商品编码 = COUNTROWS(VALUES('商品表'[商品编码]))

ALL_商品编码 = COUNTROWS(ALL('商品表'[商品编码]))

values_售价 = COUNTROWS(VALUES('商品表'[售价]))

ALL_售价 = COUNTROWS(ALL('商品表'[售价]))

  如果你对ALL处理由参照完整性不匹配而产生的空行的行为不理解的话，可以看我之前写的这篇文章：16、理解VALUES与DISTINCT，上面的例子用的数据也是之前这篇文章的，而我在之前这篇文章里已经详细介绍过了，这里就不再重复，因为ALL处理由参照完整性不匹配而产生的空行的行为与VALUES一致。

  5、由于ALL的参数可以引用多列，因此有人可能会采用引用多列的方式来返回表的所有行，这是错误用法，这里举个小例子来说明，用到的度量值公式如下：

ALL_TABLE = COUNTROWS( ALL( '销售表' ) )

ALL_COLUMNS = COUNTROWS( ALL( '销售表'[日期],'销售表'[商品编码],'销售表'[销售数量] ) )

VALUES_TABLE = COUNTROWS( VALUES( '销售表' ) )

DISTINCT_TABLE = COUNTROWS( DISTINCT( '销售表' ) )

  将上述度量值放入卡片图中，结果如下图：

  从上图中可以看到，想要通过ALL引用表的所有列来返回表的所有行的做法是错误的，这样的做法并不能返回表的所有行，即使引用了表的所有列。原因就在于当ALL的参数为列时，无论是单列还是多列，都会以行为单位去重。此外，我在这个例子中也加入了VALUES和DISTINCT的对比，如果对它们的结果不理解的话，我还是建议你去看我的这篇文章：16、理解VALUES与DISTINCT，这里就不再重复介绍。

  帮助理解的例子就到这里结束，如果还不能理解的话可以亲自去实验，ALL用作表函数需要注意的地方上面都已经列出，根据实验结果归纳总结一下很容易就能掌握。很多人都不注重细节，但细节可以决定成败！

  将ALL函数用作筛选调节器

  筛选调节器函数是特指应用在CALCULATE函数的内部筛选器中，且能改变新筛选上下文生成方式的一类函数。由于ALL函数既能用作筛选调节器，也能用作表函数，因此在遇到ALL函数时必须先判断这个ALL函数的作用。即要搞清楚ALL函数什么时候用作表函数，又是什么时候用作筛选调节器，否则很容易就会出错。简单来说，直接把ALL函数用在CALCULATE和CALCULATETABLE的筛选器参数上，那么此时的ALL类函数就是筛选调节器，除此之外的都是表函数。需要注意，如果ALL函数在CALCULATE的筛选器参数上出现，但在ALL函数的外面还套了函数，那么这样的ALL函数是用作表函数而不是筛选调节器，请重点注意上面所描述的直接这个词的含义。

  搞清楚了ALL函数什么时候用作筛选调节器，那么也是时候来看一下ALL用作筛选调节器时的行为是什么了。

  ALL在用作筛选调节器时，将移除参数所指定的表的扩展表或指定的列上的所有筛选器。ALL在用作筛选调节器时从不添加筛选器，它只会移除筛选器。ALL在作为筛选调节器时，其参数可以接受表以及多列，甚至还可以省略任何参数，具体如下：

  1、若参数为列时，ALL将移除其参数所指定的列上的筛选器，而且不会返回任何值；
  2、若参数为表时，ALL将移除其参数所指定的表的扩展表的所有列上的筛选器，而且不会返回任何值。
  3、若省略所有参数，ALL将移除所有表的所有列上的筛选器，不会返回任何值。

  下面来看一些帮助理解的例子：

  1、先来看一个简单地移除筛选的例子，用到的度量值公式如下：

总销量 = CALCULATE(SUM('销售表'[销售数量]),ALL('商品表'[商品名称]))

  新建一个矩阵，把商品表中的商品名称放入行字段，并把上述度量值放入值字段中，结果如下图：

  由于ALL函数移除了行标签所提供的筛选器，使得计算器参数的计值环境里不存在任何筛选器，所以整列都是总计值。如果把上面这个度量值稍稍修改一下，修改成这样：

总销量 = CALCULATE(SUM('销售表'[销售数量]),ALL('商品表'[商品编码]))

  那么矩阵的结果会变成如下：

  原因就在于行标签提供的筛选器是商品表中商品名称列上的筛选器，而ALL函数要移除的筛选器却是商品表中商品编码列上的筛选器，由于外部计值环境里本就不存在商品编码列上的筛选器，所以是否移除都无影响，因此计算器参数的计值环境仍然只有行标签提供的筛选器，所以修改后的度量值能够正确计算每个商品的销量。

  2、下面再来看一下ALL函数用表做参数时的例子，用到的度量值如下：

总销量-ALL销售表 = CALCULATE(SUM('销售表'[销售数量]),ALL('销售表'))

  把上述度量值继续放入值字段中，结果如下图：

  现在，行标签提供的筛选器属于商品表上的商品名称列，而ALL的参数却是销售表，按理来说应该是无影响的。但是，ALL函数的参数为表时，它的作用是移除参数所指定表的扩展表上的所有列上的筛选器。由于销售表的扩展表包含了商品表的所有列，因此行标签所提供的筛选器也会被移除，所以计算器参数所处的计值环境里不存在任何筛选器，所以整列都是总计值。

  那么关于ALL函数用作筛选调节器时的例子就介绍到这里吧，其实ALL函数用作筛选调节器时的行为很简单，就是移除指定的筛选器，难点全都在筛选器的交互以及筛选传递的理解上，而这两点在之前介绍筛选上下文与扩展表时都已经解释得很清楚了，因此我就不再啰嗦了。

  ALL函数用作筛选调节器与用作表函数的区别

  我知道，可能有很多人都不明白ALL用作筛选调节器时的行为，总是将ALL理解成返回表的表函数，即使我在上文中反复强调：ALL函数用作筛选调节器时的行为是移除参数所指定的表的扩展表或指定的列上的所有筛选器，并不会返回任何值。在大多数情况下，将本应用作筛选调节器的ALL函数理解成了返回表的表函数也是逻辑自洽的，因为CALCULATE和CALCULATETABLE的筛选器参数是可以接受表筛选的。但是当遇到某些情况时，仍然把用作筛选调节器的ALL函数理解成表函数，那么得到的结果将无法解释。

  为了能够清晰明确地观察到ALL函数用作筛选调节器时的行为是移除筛选器而不是返回表，我特地设计了一个例子来说明。由于这个例子涉及到了扩展表原理，可能会比较难理解，但我会尽可能地解释。如果你不能理解这个例子，你只需要记住：“ALL函数用作筛选调节器时的行为是移除参数所指定的表的扩展表或指定的列上的所有筛选器，并不会返回任何值。”，因为这个例子是为了说明这句话而设计的，而不是为了别的。

  这个例子使用到的数据与模型如下图所示：

  这份数据与模型我们已经使用过很多次了，在上篇文章介绍扩展表原理时也是用的这份数据。那么仔细观察，可以发现在订单表中只存在8种产品的销售记录，有5种产品是不存在销售记录的，明确这一点对将要介绍到的这个例子来说是很重要的。

  使用到的度量值公式如下：

产品表的行数_1 = CALCULATE(COUNTROWS('产品表'))

产品表的行数_2 = CALCULATE(COUNTROWS('产品表'),'订单表')

产品表的行数_3 = CALCULATE(COUNTROWS('产品表'),ALL('订单表'))

产品表的行数_4 = CALCULATE(COUNTROWS('产品表'),FILTER(ALL('订单表'),1))

  新建一个矩阵，把订单表中的产品名称放到行字段，把上述度量值放到值字段，结果如下图所示：

  先来看第一个度量值，由于行标签提供的筛选器是订单表的产品名称列上的筛选器，而产品表的扩展表上不包含下级表（订单表）的任意列，所以行标签提供的筛选器无法筛选到产品表，使得产品表的可见数据为所有数据，因此统计的就是产品表的实际行数，即13行。当然，如果不从扩展表的角度去解释的话，也可以从筛选器不能逆向筛选这个角度来解释，但这都是一样的。

  再来看第二个度量值，其使用了订单表作为内部筛选器参数，因此该订单表要先在外部计值环境里计值，所以订单表会被行标签所提供的筛选器筛选，其筛选后的可见数据再转化成筛选器元组。但因为订单表是基础表，所以其转化成的筛选器元组中包含了扩展表上的所有列，而订单表的扩展表又包含了产品表的所有列，所以其扩展表上属于产品表的那些列上的筛选器能够筛选得到产品表。但由于订单表已经被行标签所提供的筛选器筛选，使得只有与行标签对应的产品的数据，即只有单个产品的数据，所以在明细行中的值全为1，而在总计行时订单表没有被筛选，故结果为8，即存在销售记录的产品数。

  再来看第三个度量值，在这个度量值中的ALL函数直接用作了CALCULATE的内部筛选器参数，因此这里的ALL函数被用作了筛选调节器，会移除其参数所指定表的扩展表上的所有列上的筛选器，因此行标签所提供的筛选器被移除了，所以计算器参数的计值环境里不存在任何筛选器，所以产品表的可见数据为所有数据，因此统计的就是产品表的实际行数，即13行。但实际上，哪怕不移除行标签所提供的筛选器，对产品表也是无影响的，具体可参考第一个度量值的解释。

  再来看第四个度量值，在这个度量值中的ALL函数出现在了CALCULATE的内部筛选器参数中，但其外面还套了一个FILTER函数，因此在这里的ALL函数被用作了表函数，将忽略任何筛选器，返回订单表的所有行。然后，FILTER的第二参数为1，将自动转换成逻辑值TRUE，使得FILTER函数不会过滤掉任何一行，因此，整个内部筛选器参数其实就等于订单表的所有行。这么设计的原因主要就是为了与第三个度量值做对比，强制地让ALL函数作为表函数，并不更改其返回的结果。那么接下来，由于订单表是基础表，所以其转化成的筛选器元组中包含了扩展表上的所有列，而订单表的扩展表又包含了产品表的所有列，所以其扩展表上属于产品表的那些列上的筛选器能够筛选得到产品表。而且在第四个度量值中，由于ALL函数被用作了表函数，返回了订单表的所有行。所以其扩展表上属于产品表的那些列上的筛选器能够筛选得到的产品即为订单表中存在的产品，因此最终返回结果8，即存在销售记录的产品数。

  下面重点对比一下第三与第四个度量值的区别。第四个度量值已经是明确地模拟了把ALL函数理解成表函数时的情况，得到的结果是8。如果把第三个度量值中的ALL函数理解成返回表的表函数，那么第三个度量值的结果也应该为8才对，但实际上第三个度量值的结果却是13，因此已经足以说明这一点：ALL函数用作筛选调节器时的行为是移除参数所指定的表的扩展表或指定的列上的所有筛选器，并不会返回任何值。

  那么ALL函数用作筛选调节器与用作表函数的区别就介绍到这里，下面来看一个ALL函数的简单应用。

  利用ALL函数求总计百分比

  一般情况下，求解某个指标在总计里的占比是很常见的，在学习了ALL函数之后，就可以利用ALL函数能忽略或移除筛选器的特点来求解。下面来看一个求解各个产品的销量占总销量的百分之几的例子，用到的度量值公式如下：

销售数量 = SUMX('订单表','订单表'[数量])

总销售数量 = SUMX(ALL('订单表'),'订单表'[数量])

销量占比 = DIVIDE([销售数量],SUMX(ALL('订单表'),'订单表'[数量]))

  其中，DIVIDE函数是安全除法，能接受三个参数，第一个参数是分子，第二个参数是分母，第三个参数是分母为零时的替代结果（省略默认为空）。

  新建一个矩阵，把订单表的产品名称放到行字段，并把上面三个度量值放到值字段，结果如下：

  得到的结果符合预期，但如果将[销量占比]这个度量值的公式修改成如下：

销量占比-错误 = DIVIDE([销售数量],SUMX(ALL('订单表'[数量]),'订单表'[数量]))

  得到的结果将有所变化，如下图所示：

  可以看到，修改后的度量值计算的销量占比的结果完全是错误的，造成这个问题的原因，就是因为ALL用作表函数时的参数若为列时，ALL将忽略任何对其参数起作用的筛选器，返回一列或多列的所有值去重后的结果。由于去重的原因，使得计算销量占比时的分母变小了，得到的结果自然就大了。

  为了避免这种逻辑没问题但因为函数特性而引起的错误，建议使用移除筛选的方式来计算各种指标的占比。例如，将[销量占比]这个度量值的公式修改成如下：

销量占比-移除筛选 = DIVIDE([销售数量],CALCULATE([销售数量],ALL('订单表'[产品名称])))

  那么关于ALL函数的所有细节与用法都已经介绍完毕了，剩下的就是靠个人自己去练习与总结了。ALL函数是DAX中非常重要的一个核心函数，因此必须掌握清楚它的每一个细节，要清楚什么时候是用作表函数，以及什么时候是用作筛选调节器，并且要了解参数为列与为表时的区别。可以说，DAX中的一个ALL函数的用途抵得上Excel中的十个函数，可见其复杂性与重要性。

  DAX系列文章中涉及到的案例文件，均已上传到QQ群：344353627，若有需要，可自行加群获取。