理解 ALLSELECTED

显示全部楼层 · 2021-2-21 19:03:06

ALLSELECTED 是最复杂的 DAX 函数，本文完整的介绍了它的复杂性。但是，对于绝大部分用户，你可以不必深究这背后的原理，理解初识 ALLSELECTED 部分可以帮助你解决大部分问题

初识 ALLSELECTED

当你想把透视表的页面筛选器或切片器作为计算使用的参数时，ALLSELECTED 是一个非常有用的函数。例如，假设你希望构建如图所示的报告。

透视表中显示的百分比是根据当前显示的总数计算的，而不是所有颜色的总数

在这个报告中，显示了当前行的销售额占列总计的比重。这个百分比难以计算的原因是，产品颜色被同时用作切片器（在这个例子中，我们只选择了部分颜色）和行标签。如果你借助到目前为止获得的知识，可以试试这个公式：

[SalesPct] :=
DIVIDE (
[Sales Amount],
CALCULATE (
[Sales Amount],
ALL ( Product[Color] )
)
)

复制代码

使用 ALL (Product[Color])从颜色列中移除了筛选器，并尝试在列级别计算总数。不幸的是，ALL 移除了来自行和切片器的所有筛选，这将导致一个错误的结果。你可以从下面的数据透视表中看到公式的结果，其中总计不显示 100%，而是一个较小的值。

使用 ALL 函数计算的百分比是不准确的，因为它是针对所有颜色的百分比

这里的问题是计算分母时使用了所有颜色的数据，即使用户只在切片器中选择了其中一部分颜色。对于每一行，计算时使用的分母要大于透视表实际显示的总数。

我们需要的是一个不返回所有颜色，但可以返回初始筛选上下文中所有选择颜色的函数，即一个完整的透视表。我们将这种计算称为视觉总计（Visual Totals），因为它使用用户可见的总计代替整个数据模型范围内的总计，使用的函数是 ALLSELECTED。如果你这样定义公式：

[SalesPct] :=
DIVIDE (
[Sales],
CALCULATE (
[Sales],
ALLSELECTED ( Product[Color] )
)
)

复制代码

结果将是本节开头所示的正确结果。

ALLSELECTED 只返回透视表在初始筛选上下文中的可见值。换句话说，ALLSELECTED 忽略透视表行和列上的筛选器，只考虑用于计算总计的筛选器。

ALLSELECTED 的三种参数

ALLSELECTED 支持三种不同类型的参数调用：

单列或多列，例如 ALLSELECTED (Product[Color])，返回初始筛选的颜色。
整张表，例如 ALLSELECTED (Product)，对表的所有列执行 ALLSELECTED，返回其中所有初始选择的行。
你还可以使用不带参数的 ALLSELECTED()，它在数据模型的所有表上执行 ALLSELECTED 操作，从而可以在不含行和列筛选器的情况下计算透视表的总计。

ALLSELECTED 被用来以非常动态的方式计算百分比和比率。接下来，我们将更深入地介绍 ALLSELECTED，它隐藏了一些复杂内容，这使它成为DAX 中最复杂的函数。

理解 ALLSELECTED

基于我们目前的了解，ALLSELECTED 看起来像一个特殊的函数，能够理解用户在透视表中选择的内容。实际上，它允许你检索透视表运行时产生的影子筛选上下文（Shadow Filter Context）。不幸的是，对 ALLSELECTED 的描述存在一个大问题，即 DAX 函数如何了解用户在数据透视表中做的设置。如果我们使用的不是透视表而是 DAX 查询，那么 ALLSELECTED 是否仍然有效？为了解决这个合理性问题，我们需要深入研究 ALLSELECTED，以便准确理解它是如何运行的。

引言

让我们在阐述开始之前先公布这个简单的事实：ALLSELECTED 并不知道用户对透视表所做的操作，它甚至都不知道透视表的存在。那么，它是究竟是如何工作的呢？

译者注：ALLSELECTED 是一个如此复杂且充满陷阱的函数，以至于在 DAX 权威指南第一版面世的时候，对它的介绍仍然不够系统和完整，并且存在错误描述，后来作者已经对此做出了修正，最新的文章发布在这里，为了保证中文版内容的准确和完备，经作者同意，本节内容使用发表于 SQLBI 的《The Definitive Guide to ALLSELECTED》，原书内容不再提供，敬请谅解。

我们已经写过很多关于 ALLSELECTED 的文章，不幸的是，我们从来没有完全搞清楚它的行为。原因很简单。我们陷入了 ALLSELECTED 制造的众多陷阱之一：我们相信自己理解了它的原理，其实我们只是在不断接近真相 – 但尚未抵达。

在花费大量时间研究了 ALLSELECTED 的行为并与 Analysis Services 开发团队讨论了这一问题之后，我们终于完全理解了 ALLSELECTED 函数。本文描述了它的行为。如果你已经阅读了第一版“DAX 权威指南”，请将这篇文章视为该书的一个勘误表吧。事实上，本文内容相比 DAX 指南成书的时候更加精确和清晰。对此我们深表歉意。

在文章开始之前重申：这不是一篇关于如何使用 ALLSELECTED 的介绍性文章。我们并不是在解释什么时候使用这个函数，以及用它来做什么。一篇只涉及 ALLSELECTED 内部原理的文章就已经撰写了大约 20 页；继续添加介绍将超出本文的范围。这篇文章的受众是想深入了解 ALLSELECTED 原理的读者。

让我们从结尾开始，告诉你 ALLSELECTED 做了什么。第一次阅读的时候读者可能很难理解下面这段陈述。事实上，整篇文章的目的是解释这句话：

ALLSELECTED 既可以返回表，也可以移除筛选器并恢复之前的筛选上下文。这两种功能的实现，都是通过访问和使用迭代器在筛选上下文堆栈中留下的最后一个影子筛选上下文实现的。

表函数还是 CALCULATE 调节器？

在进一步深入之前，我们需要回答一个重要的问题：ALLSELECTED 是一个表函数，还是像 KEEPFILTERS 和 USERELATIONSHIP 那样的 CALCULATE 调节器？这取决于参数的数量和函数所在的上下文。事实上，ALLSELECTED 可以与三种不同的参数一起使用：表、列或完全没有参数，如下面的公式所示:

AllSelectedColumn :=
CALCULATE (
[SalesAmount],
ALLSELECTED ( Customer[Occupation] )
)
AllSelectedTable :=
CALCULATE (
[SalesAmount],
ALLSELECTED ( Customer )
)
AllSelectedAll :=
CALCULATE (
[SalesAmount],
ALLSELECTED ()
)

复制代码

下图显示了在包含产品类别、客户性别和职业的矩阵中使用这三种度量值的结果：

此示例说明 ALLSELECTED 可以作为 CALCULATE 调节器。尽管如此，ALLSELECTED 也可以用作表函数，如以下代码中所示：

AllSelectedCustomerSales :=
SUMX (
ALLSELECTED ( Customer ),
[SalesAmount]
)

复制代码

当作为表函数使用时，ALLSELECTED 将遵循下面解释的规则返回表的一个子集，或列值的一个子集。另一方面，在不使用任何参数的情况下，如以下代码所示，ALLSELECTED 只能作为 CALCULATE 调节器使用:

AllSelectedSales :=
CALCULATE (
[SalesAmount],
ALLSELECTED ()
)

复制代码

现在，重点是 ALLSELECTED 与表或列一起使用时的行为，稍后我们将介绍不带参数的 ALLSELECTED 作为 CALCULATE 调节器的行为。在对 ALLSELECTED 进行更深入的分析之前，我们需要引入影子筛选上下文，因为它们在 ALLSELECTED 的描述中至关重要。

介绍影子筛选上下文

影子筛选上下文是由迭代器创建的一种特殊类型的筛选上下文，在初始状态下是不活动的。不活动的筛选上下文停留在休眠状态，它不会以任何方式影响公式计值流。尽管如此，它仍然存在，而且对于本文来说非常重要，因为 ALLSELECTED 在执行时激活了影子筛选上下文。让我们观察下面这个度量值:

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

复制代码

作为迭代器，SUMX 生成一个包含销售表的影子筛选上下文。它不包含整个销售表。只包含其在当前筛选上下文中可见的行。作为一个影子筛选上下文，它是不活动的。因此不会影响计算，这就是为什么影子筛选上下文没有被广泛讨论的原因。为了演示这一点，我们将使用以下代码，它们的行为与预期一致。结果是销售总量乘以颜色的数量。在缺少上下文转换的环境中调用 SUM 是有意为之：

WrongSalesAmount :=
SUMX (
VALUES ( Product[Color] ),
SUM ( Sales[Quantity] )
)

复制代码

在迭代过程中，颜色列的行上下文不会转换为筛选上下文，因为没有 CALCULATE 执行上下文转换。正如我们介绍的，这里确实存在一个筛选上下文：“影子”筛选上下文。影子筛选上下文包括迭代器启动时当前筛选上下文中活动的颜色列表。实际上，影子筛选上下文在迭代过程中是不活动的，除非它被一个叫做 ALLSELECTED 的函数激活。换句话说，当不使用 ALLSELECTED 时，忽略影子筛选上下文的存在是安全的。另一方面，当你决定使用 ALLSELECTED 时，影子筛选上下文变得至关重要。

细心的读者可能会注意到，上面的公式中即使影子筛选上下文是活动的，它也不会改变结果。事实上，由于影子筛选上下文包含了所有颜色，所以结果与初始筛选上下文相同。但是，随着下面这个表达式的出现，复杂性开始增加：

AnotherSalesAmount :=
SUMX (
CALCULATETABLE (
VALUES ( Product[Color] ),
Product[Color] = "Red"
),
SUM ( Sales[Quantity] )
)

复制代码

在这种情况下，影子筛选上下文包含了一个对颜色的选择——即，只有红色。但是在迭代中，SUM (Sales[Quantity])仍然计算所有销售的总和。如果影子筛选上下文是活动（生效）的，那么引擎将只对红色产品的销售进行求和。

在本文中，我们将普通筛选器（即非影子筛选器）称为显式筛选器，惟一的目的是在需要时区分显式筛选器和影子筛选器。

现在我们已经了解了影子筛选上下文，我们可以开始分析基于示例数据的 ALLSELECTED 的行为。

样例数据

我们使用一个有 9 行的表, 并且只有三列： “产品”、”品牌” 和 “颜色”:

在下面的查询中，我们使用外部的 CALCULATETABLE 来筛选表格中的某些颜色和品牌。我们还应用了一个不常见的度量值，ListProductNames。ListProductNames 使用 CONCATENATEX 返回在当前筛选上下文中可见的产品名称列表。此度量值的目的是帮助我们分析 ALLSELECTED 在用作表函数时的结果和用作 CALCULATE 调节器时的结果。

DEFINE
MEASURE Product[ListProductNames] =
CONCATENATEX ( VALUES ( 'Product'[Product] ), Product[Product], ", " )
EVALUATE
CALCULATETABLE (
ROW ( "Products", [ListProductNames] ),
Product[Color] IN { "Blue", "Green" },
Product[Brand] IN { "Contoso", "Fabrikam" }
)

复制代码

查询的结果是：Helmet, Shoes, Keyboard, Piano。下图显示了为颜色列和品牌列生成的两个筛选器，以及最终得到的筛选上下文：

初步来看，似乎 ALLSELECTED 恢复了来自外部 CALCULATETABLE 的筛选器。然而，情况并非如此，尽管它返回相同的结果。所发生的是 ADDCOLUMNS(迭代器)生成一个包含迭代表的影子筛选上下文。迭代表是 VALUES(Product[Brand])的结果，公式在只包含 Contoso 和 Fabrikam 的筛选上下文中计算，返回这两个品牌。

ALLSELECTED 返回在最后一个影子筛选上下文中可见的品牌，生成预期的结果。如何才能确定这确实是 ALLSELECTED 的行为呢？我们可以尝试用 ALL 替换 VALUES，这时 ADDCOLUMNS 的迭代就不再发生在两个品牌上，而是在所有品牌上：

EVALUATE
CALCULATETABLE (
ADDCOLUMNS (
ALL ( 'Product'[Brand] ),
"Brands", CONCATENATEX (
ALLSELECTED ( 'Product'[Brand] ),
Product[Brand],
", "
)
),
Product[Color] IN { "Blue", "Green" },
Product[Brand] IN { "Contoso", "Fabrikam" }
)

复制代码

这一次，结果将是一个三行表，每一行显示所有品牌:

报告中只选择了 Contoso 和 Fabrikam 两个品牌，一共 6 个产品。AllSel 的值看起来是正确的，因为它在每一行计算了当前矩阵的总计数量。但 AllSelSumX 的结果令人困惑:唯一看起来正确的是底部总计（300×6 = 1800），而其他数字似乎都错了。

注意：如果你对 ALLSELECTED 还停留在基础理解层面，这些数字看起来是错误的。事实上，一旦清楚了 ALLSELECTED 的工作原理，这些结果是完全正确的。

事实上，如果我们关注 30 （报告中 Contoso/Blue 所在的行）这个值，那么解释就很简单了。我们从只包含一个产品、一个品牌、一种颜色的筛选上下文开始。两个嵌套迭代（品牌列上的 SUMX 和颜色列上的 SUMX）都只迭代一行，这意味着两个 SUMX 函数都生成一个只包含一个值的影子筛选上下文。ALLSELECTED 将恢复这些影子筛选上下文，所以 SUM 在一个只包含一行的筛选上下文中执行。因此，为每一行计算的值是在给定行的筛选上下文中唯一可见的产品的值，因为 ALLSELECTED 通过激活两个影子筛选上下文将其重新激活。遵循相同的路径，AllSelSumX 的所有值开始变得可解释，因为现在我们已经清楚了 ALLSELECTED 是如何工作的，即恢复影子筛选上下文。我们强烈建议读者在阅读下面的提示之前，花一点时间来理解 Contoso 所在行的结果：

180 等于 60 乘以 3；公式对品牌列进行一次迭代；对颜色列进行三次迭代；并且，最内层的影子筛选上下文在颜色列上包含了三种颜色。

以上内容不是为了说服你 AllSelSumX 显示的值有任何意义。很可能报告的用户和读者都认为这个数字是错误的。本文的目标不是找到一种计算指标的正确方法。相反，我们的目的是专注于理解 ALLSELECTED 是如何计值的，我们只在真正需要使用这个函数的时候才考虑它。

ALLSELECTED 和上下文转换

在《DAX 权威指南》的第一版中，我们在介绍 ALLSELECTED 时提到 ALLSELECTED 删除了由上下文转换生成的最后一个筛选上下文。不幸的是，这是一个错误的描述。这需要进一步解释：

ALLSELECTED 到底是否与上下文转换的过程发生交互？我们知道，作为创建筛选上下文的一部分，CALCULATE 生成的筛选上下文等价于任何现有的行上下文。我们还知道，这个由上下文转换生成的筛选上下文相对于任何显式筛选上下文具有较低的优先级。使用 ALLSELECTED 作为 CALCULATE 筛选器参数意味着将影子筛选上下文转换为显式筛选上下文。通过这种方式，它的优先级将超过由上下文转换生成的筛选上下文。

因此，看起来 ALLSELECTED 移除了由上下文转换生成的最后一个筛选上下文。实际上并不存在这种移除。这个移除效果只是将影子筛选上下文转换为显式筛选上下文的副带作用。一旦 ALLSELECTED 的语义可以通过影子筛选上下文清晰的解释，理解它的行为就会变得容易得多。
总结

正如本节介绍的，只有当你更熟悉影子筛选上下文时，ALLSELECTED 的行为才更容易理解。也就是说，因为影子筛选上下文的存在，ALLSELECTED 变成了一个非常复杂的函数，而且它与显式筛选上下文的交互使得完整描述计值过程变得困难。

对于 ALLSELECTED 的使用建议是，当且仅当没有迭代发生时，使用 ALLSELECTED 来检索查询上下文，也就是透视表或报表所在的计值环境的上下文。如果有任何迭代发生，建议避免使用 ALLSELECTED，因为结果几乎是不可预测的，并且也是非常复杂和难以理解的。本文介绍了理解 ALLSELECTED 行为所需的所有工具。但是，我们不希望每次需要调试度量值时都必须执行所有这些复杂的推理步骤。因此，这个建议始终成立：ALLSELECTED 不应该在迭代中使用，除非用户已经非常清楚他们在做什么，并且有强烈的需求。在大多数情况下，变量可以避免在迭代中使用 ALLSELECTED。我们强烈建议你使用变量。

7114211936397.zip (41.54 KB, 下载次数: 0)