3.4 ALL和ALLEXCEPT

第3章 使用基本表函数

3.1 表函数简介
3.2 EVALUATE语法简介
3.3 理解FILTER
3.4 ALL和ALLEXCEPT
3.5 了解VALUES，DISTINCT和空行
3.6 将表用作标量值
3.7 ALLSELECTED介绍

ALL和ALLEXCEPT介绍

在上一节中，您学习了FILTER，这在我们想要限制表中的行数时非常有用。有时我们想做相反的事情；也就是说，我们希望扩展用于特定计算的行数。DAX提供了一组为此目的而设计的功能：ALL，ALLEXCEPT，ALLCROSSFILTERED，ALLNOBLANKROW和ALLSELECTED。在本节中，您将学习ALL和ALLEXCEPT，而本章后面将介绍后两者，ALLCROSSFILTERD将在第14章“高级DAX概念”中介绍。

ALL根据使用的参数返回表的所有行或一个或多个列的所有值。例如，以下DAX表达式返回一个ProductCopy计算表，其中包含Product表中所有行：

ProductCopy = ALL ( 'Product' )

注意
在计算表中不需要ALL，因为没有报告筛选会影响它。但是，在度量值中ALL是用用的，如下面的示例所示。

当我们需要计算百分比或比率时，ALL都会非常有用，因为它会忽略报表自动引入的筛选。想象我们需要一个如图3-3所示的报告，该报告在同一行中同时显示销售额和给定金额占总数的百分比。

图3-3 该报告显示了销售额和相对于总计百分比

度量值Sales Amount通过迭代Sales表并执行Sales [Quantity]与Sales [Net Price]的乘积来计算值：

Sales Amount :=
SUMX (
    Sales,
    Sales[Quantity] * Sales[Net Price]
)

要计算百分比，我们将销售额除以总计。因此，即使报表故意过滤一个给定类别，该公式也必须计算总销售额。这可以通过使用ALL函数来获得。实际上，无论将什么筛选应用于报表，以下度量都会产生所有销售额的总数：

All Sales Amount :=
SUMX (
    ALL ( Sales ),
    Sales[Quantity] * Sales[Net Price]
)

在公式中，我们充分利用了ALL函数，将对Sales的引用替换为ALL（Sales）。在这一点上，我们可以通过执行简单的除法来计算百分比：

Sales Pct := DIVIDE ( [Sales Amount], [All Sales Amount] )

图3-4一起显示了三个度量值的结果：

图3-4度量值*All Sales Amount*计算结果总是等于总计金额

ALL的参数不能是表表达式。它必须是表名或列名列表。您已经了解了ALL对表的作用。如果我们改为使用列，其结果是什么？在这种情况下，ALL将返回整个表中该列的所有不同值。计算表Categories从Product表的* Category*列获得：

Categories = ALL ( 'Product'[Category] )

图3-5显示了Categories计算表的结果。 此报告仅显示类别。

图3-5 ALL与列一起使用会产生该列的不同值的列表

我们可以在ALL函数的参数中从同一表中指定多个列。在这种情况下，ALL将返回这些列中所有现有的值组合。例如，我们可以通过将Product [Subcategory]列添加到值列表中来获得所有类别和子类别的列表，获得如图3-6所示的结果：此报告显示每个类别的子类别。

Categories =
ALL (
    'Product'[Category],
    'Product'[Subcategory]
)

图3-6 该列表包含类别和子类别的不同的现有值

在所有变化中，ALL都会忽略任何现有筛选以产生结果。我们可以将ALL用作迭代函数（例如SUMX和FILTER）的参数，也可以将其用作CALCULATE函数中的筛选参数。您将在第5章中学习CALCULATE函数。

如果我们要在ALL函数调用中包括表的大多数（但不是全部）列，则可以改用ALLEXCEPT。ALLEXCEPT的语法需要一个表，后跟我们要排除的列。结果，ALLEXCEPT返回一个表，该表具有原表其他列中现有值组合的唯一列表。

ALLEXCEPT是一种编写DAX表达式的方法，该表达式将自动在结果中包括将来可能出现在表中的任何其他列。例如，如果我们有一个包含五列（ProductKey，Product Name，Brand，Class，Color）的Product表，则以下两个表达式将产生相同的结果：

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]
)

换句话说，使用ALL可以声明所需的列，而使用ALLEXCEPT则可以声明要从结果中删除的列。在高级计算中，ALLEXCEPT主要用作CALCULATE的参数，很少使用较简单的公式。因此，即使我们在这里为了完整起见也包含了它的描述，它仅在以后的学习中才有用。

热门类别和子类别
作为ALL用作表函数的示例，假设我们要生成一个仪表板，以显示销售额超过平均销售额两倍的产品的类别和子类别。要生成此报告，我们需要首先计算每个子类别的平均销售额，然后在确定该值之后，从子类别列表中检索销售额大于该平均值两倍的那些。
以下代码生成了该表，值得深入研究一下，以了解表函数和变量的功能：

BestCategories =
VAR Subcategories =
   ALL ( 'Product'[Category], 'Product'[Subcategory] )
VAR AverageSales =
   AVERAGEX (
       Subcategories,
       SUMX ( RELATEDTABLE ( Sales ), Sales[Quantity] * Sales[Net Price] )
   )
VAR TopCategories =
   FILTER (
       Subcategories,
       VAR SalesOfCategory =
           SUMX ( RELATEDTABLE ( Sales ), Sales[Quantity] * Sales[Net Price] )
       RETURN
           SalesOfCategory >= AverageSales * 2
   )
RETURN
   TopCategories

第一个变量Subcategories（子类别）存储所有类别和子类别的列表。然后，第二个变量AverageSales计算每个子类别的平均销售额。最后，第三个变量TopCategories从子类别中删除销售额不超过AverageSales值两倍的子类别。

该表的结果在图3-7中可见。

图3-7销量为平均销量的两倍以上类别
一旦掌握了CALCULATE和FILTER上下文后，便可以使用更短、更有效的语法编写相同的计算。但是，在此示例中，您已经了解了结合表函数如何产生强大的结果，这对于仪表板和报表非常有用。