22、理解ALLEXCEPT函数

本篇文章将介绍ALLEXCEPT函数的使用，ALLEXCEPT函数的使用频率没有ALL函数高，但在某些情况下却是很有用的。当我们需要移除多个筛选器并保留少数筛选器时，使用ALLEXCEPT函数能够大大减少代码的书写量。

ALLEXCEPT函数的语法结构

• 语法：

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

• 作用：

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

1、若ALLEXCEPT用作表函数时，将忽略任何筛选器，返回第一参数的表的扩展表中除所指定的列之外的所有列的现有值的唯一值组合，但扩展表上的列不可见。

2、若ALLEXCEPT用作筛选调节器时，将移除第一参数指定的表的扩展表中除所指定列之外的其余列上的所有筛选器。

ALLEXCEPT函数的第一参数必须为基础表，第二参数可以是一个或多个列，这些列必须来自第一参数所指定的基础表的扩展表上的列。此外第二参数还有个隐藏用法，就是直接使用基础表，等价于把该基础表转换成其扩展表上存在的多个列。

本篇文章使用到的数据与数据模型如下图所示：

将ALLEXCEPT函数用作表函数

若ALLEXCEPT用作表函数时，将忽略任何筛选器，返回第一参数的表的扩展表中除所指定的列之外的所有列的现有值的唯一值组合，但扩展表上的列不可见。

为了更好地理解这句话，下面用一个简单的例子来描述：假设有某个表具有四列，列名分别为：A、B、C、D，那么当ALLEXCEPT函数的第二参数指定了A列，那么将把BCD三列完整提取出来形成一个表，然后再对这个表按行为单位进行去重，然后ALLEXCEPT函数返回这个表去重后的结果。

其实上面的这个简单例子可以用ALL函数等价表示出来，即：

// 该等价写法只在ALLEXCEPT函数用作表函数时等价

ALLEXCEPT('TableName','TableName'[ColumnName1])

----------------假设表只有四列，那么ALLEXCEPT等价于---------------

ALL('TableName'[ColumnName2],'TableName'[ColumnName3],'TableName'[ColumnName4])

注意：上面这个等价的写法其实并不等价，只是为了方便大家理解ALLEXCEPT函数的行为才给出的，实际上还需要考虑扩展表，而且返回结果中的扩展表上的列是不可见的。

下面来看一些帮助理解的例子，为了方便起见，使用查询来进行演示：

在上面这个例子中，虽然有一个内部筛选器，但是ALLEXCEPT在作为表函数时是会忽略任何筛选器的，因此该内部筛选器可以当作不存在。然后，ALLEXCEPT返回订单表的扩展表上除了'订单表'[产品名称]外的其他列去重后的结果，但其中属于扩展表上的列不可见，因此最终返回的可见部分就只有订单表上除产品名称外的其他三个列。该例子中ALLEXCEPT的行为如下图所示：

由于ALLEXCEPT的第二参数可以是来自第一参数的基础表的扩展表上的列，因此在使用ALLEXCEPT时需要时刻注意要以扩展表的视角来看待其行为，比如下面这个例子：

在这个例子中，ALLEXCEPT的第二参数使用了订单表的扩展表上的字段，返回的是整个订单表去重后的结果，它的计值行为如下图所示：

通过上面这两个例子，相信都能理解ALLEXCEPT在用作表函数时的行为，但这两个例子可能没体现出扩展表的影响，因为在返回结果中扩展表上的其他列是不可见的。为了更好的理解为何要以扩展表的视角来理解ALLEXCEPT函数，下面再来看一个例子：

由于ALLEXCEPT返回的表是包含扩展表上的列的，因此可以基于扩展表上的列的筛选器来实现逆向筛选，从而观察到ALLEXCEPT对扩展表的影响。在上面这个例子中，ALLEXCEPT返回的结果与行为如下图所示：

然后FILTER过滤ALLEXCEPT的返回结果，只保留A产品的数据。

然后将过滤后的数据作为CALCULATETABLE的筛选器参数，此时，订单表的四个列以及产品表的产品类别列都会被设置筛选器，然后由于筛选器无法逆向筛选，或者说产品表的扩展表不包含订单表的任何列，因此来自订单表的那四个筛选器都无法筛选产品表，只有来自产品表的产品类别筛选器能够筛选到产品表，因此最终产品表返回类别1的数据。

虽然ALLEXCEPT返回的表中看不到扩展表上的列，但是通过上面这个例子也可以间接观察到ALLEXCEP的行为是以第一参数的扩展表为目标来操作的，所以在使用ALLEXCEPT时需要时刻注意要以扩展表的视角来理解。

此外，ALLEXCEPT的第二参数是可以指定多个列的，比如：

但是不能把第一参数的表的所有字段都排除掉，至少需要保留一列，否则将会报错，如下图所示：

以上就是ALLEXCEPT在用作表函数时的行为了，只要注意以扩展表的视角来理解，并记住会忽略任何筛选器就可以了。

将ALLEXCEPT函数用作筛选调节器

当ALLEXCEPT函数用作筛选调节器时，将移除第一参数指定的表的扩展表中除所指定列之外的其余列上的所有筛选器。

ALLEXCEPT在作为筛选调节器时，其行为不再是返回表，而是与ALL一样，是移除现有的筛选器，只不过ALLEXCEPT属于批量移除，移除的范围为：第一参数指定的表的扩展表上除了第二参数指定的列之外的其他列上的筛选器。

下面通过一个简单的例子来辅助理解ALLEXCEPT作为筛选调节器时的行为，如下图所示：

在这个例子中，外层CALCULATETABLE中定义了三个不同的筛选器，然后使用ALLEXCEPT来移除筛选器，移除的范围为：订单表的扩展表上除了产品表的产品类别外的其他列上的筛选器。因此最终只有产品表的产品类别筛选器能保留下来，其他两个筛选器都被移除了，所以最终返回类别1的订单表数据。

ALLEXCEPT移除的筛选器范围如下图所示：

ALLEXCEPT用作筛选调节器时的行为比较简单，而且应该也比较好理解，只要搞清楚会被移除的筛选器范围就可以了。

ALLEXCEPT第二参数的隐藏用法

前面提到过，ALLEXCEPT的第二参数还有个隐藏用法，就是直接使用基础表，其等价于把该基础表转换成其扩展表上存在的多个列。如下所示：

//假设Table_B只有四列，且Table_B是Table_A的上级表

ALLEXCEPT('Table_A','Table_B'[Column1],'Table_B'[Column2],'Table_B'[Column3],'Table_B'[Column4])

等价于：

ALLEXCEPT('Table_A','Table_B')

在第二参数中使用表时，只能指定第一参数的表的上级表，而且不能与第一参数的表相同。

此外，该隐藏用法只是在语法层面上的，不会影响ALLEXCEPT的行为，相当于一个语法糖，具体行为还要看是用作表函数还是用作筛选调节器，可结合上面给出的等价写法进行理解。

总结

ALLEXCEPT函数与ALL函数类似，都是既可以用作表函数又可以用作筛选调节器，但功能强大的同时也带来了更多的复杂性，因此需要多多练习与总结。