从数据到信息
从信息到洞察

理解 ALLSELECTED

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 是一个表函数,还是像 KEEPFILTERSUSERELATIONSHIP 那样的 CALCULATE 调节器?这取决于参数的数量和函数所在的上下文。事实上,ALLSELECTED 可以与三种不同的参数一起使用:表、列或完全没有参数,如下面的公式所示:

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


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


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

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

https://www.sqlbi.com/wp-content/uploads/AllSelected-Internals-00.png

此示例说明 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 行的表, 并且只有三列: “产品”、”品牌” 和 “颜色”:

https://www.sqlbi.com/wp-content/uploads/AllSelected-Internals-01.png

在下面的查询中,我们使用外部的 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。下图显示了为颜色列和品牌列生成的两个筛选器,以及最终得到的筛选上下文:

https://www.sqlbi.com/wp-content/uploads/AllSelected-Internals-02.png

到目前为止,还没有什么新的东西。接下来,我们将使用加入含 ALLSELECTED 的代码片段来分析其行为。

使用列作为参数

ALLSELECTED (Table[Column])返回筛选该列的最后一个影子筛选上下文中可见的列值。在下面的代码中,我们在 Product[Product]上使用 ALLSELECTED 来检索在最后一个影子筛选上下文中可见的所有产品名称的列表。在继续阅读之前,你可以试着猜测一下结果。

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

第一种猜测是按照之前图片中的筛选器逻辑进行操作,得到和前面一样的结果:只有四个产品符合颜色是{ “Blue”, “Green” },品牌是{ “Contoso”, “Fabrikam” },即 Helmet, Shoes, Keyboard, Piano。但实际上,结果完全不同,查询返回了所有产品:

Bike, Helmet, Shoes, Robot, Shirt, Rollerblades, Motorbike, Keyboard, Piano

实际上,ALLSELECTED 返回由最后一个影子过筛选上下文过滤的列值。你可能已经注意到,在调用 ALLSELECTED 时没有活动的迭代发生。所以没有需要激活的影子筛选上下文。因此,所有的产品名称都会被返回,因为颜色和品牌都不会直接筛选 Product [Product]列,它是通过交叉筛选被影响的。

这是我们学到的第一课:ALLSELECTED 不考虑筛选上下文。它的主要目的是检索之前设置的影子筛选上下文。ALLSELECTED 与 VALUES 有很大区别。VALUESDISTINCT 总是会考虑筛选上下文,而 ALLSELECTED 不会。它在单个列上工作,并检查该列是否被影子筛选上下文过滤,忽略任何交叉筛选。

用下面这种方式改写前面的公式可能让你感到困惑:

EVALUATE
CALCULATETABLE (
    ROW (
        "Products", CALCULATE (
            CONCATENATEX ( VALUES ( 'Product'[Product] ), Product[Product], ", " ),
            ALLSELECTED ( Product[Product] )
        )
    ),
    Product[Color] IN { "Blue", "Green" },
    Product[Brand] IN { "Contoso", "Fabrikam" }
)

这一次查询返回预期的结果:即 Helmet, Shoes, Keyboard, Piano。当我们理解公式时,我们倾向于认为这是因为 ALLSELECTED (Product[Product])在 Product[Product]列上应用了一个筛选器。但事实并非如此。在这种情况下,ALLSELECTED 不会对 Product[Product]列应用任何筛选器,因为没有影子筛选上下文。当公式被调用时,VALUES 通过观察位于颜色和品牌上的筛选器来分析当前上下文中可见的产品。ALLSELECTED 对此筛选过程未做任何干预。换句话说,对 Product[Product]进行筛选的并不是 ALLSELECTED,而是颜色和品牌的交叉筛选效果,通过 VALUES 实现。

到目前为止,我们已经看到 ALLSELECTED 还没有以任何方式筛选列。现在是时候生成一个影子筛选上下文,让 ALLSELECTED 来为我们激活它了。为此,我们需要构造迭代计算——用 ADDCOLUMNS 替换 ROW,来迭代品牌列,如下面的代码所示:

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

查询返回预期的结果,为每个品牌返回 Contoso、Fabrikam。

https://www.sqlbi.com/wp-content/uploads/AllSelected-Internals-03.png

初步来看,似乎 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" }
)

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

https://www.sqlbi.com/wp-content/uploads/AllSelected-Internals-04.png

原因在于 ADDCOLUMNS 遍历的是 ALL(Product[Brand])。因此,影子筛选上下文包含 Product[Brand]的所有值。被激活后,影子筛选上下文显示所有值,尽管外层的 CALCULATETABLE 只筛选了三个品牌中的两个。

现在,我们通过在同一列上使用两个迭代器让情况变得更加复杂。考虑语法规范,我们需要使用 SELECTCOLUMNS 重命名列,以避免名称冲突。下面是一个更详细的例子:

EVALUATE
CALCULATETABLE (
    GENERATE (
        SELECTCOLUMNS (
            ALL ( 'Product'[Brand] ),
            "Outer Brand", Product[Brand]
        ),
        GENERATE (
            SELECTCOLUMNS (
                VALUES ( 'Product'[Brand] ),
                "Inner Brand", Product[Brand]
            ),
            ROW (
                "Brands", CONCATENATEX (
                    ALLSELECTED ( 'Product'[Brand] ),
                    Product[Brand],
                    ", "
                )
            )
        )
    ),
    Product[Color] IN { "Blue", "Green" },
    Product[Brand] IN { "Contoso", "Fabrikam" }
)

在同一个 Product[Brand]列上有两个嵌套迭代,在内层的度量值中我们使用 ALLSELECTED 作为表函数。结果将是内层影子筛选上下文中迭代的品牌列表。由于内层影子筛选上下文是由扫描 VALUES(Product[Brand])的迭代生成的,并且由于 VALUES 被最外层 CALCULATETABLE 创建的筛选上下文筛选,结果中的 Inner Brand 包含两个品牌:

https://www.sqlbi.com/wp-content/uploads/AllSelected-Internals-05.png

交换内部和外部品牌列使用的 VALUES 和 ALL 函数会得到不同的结果,实际上,内层的迭代扫描 ALL 生成的表,ALLSELECTED 将返回所有品牌的值。

EVALUATE
CALCULATETABLE (
    GENERATE (
        SELECTCOLUMNS (
            VALUES ( 'Product'[Brand] ),
            "Outer Brand", Product[Brand]
        ),
        GENERATE (
            SELECTCOLUMNS (
                ALL ( 'Product'[Brand] ),
                "Inner Brand", Product[Brand]
            ),
            ROW (
                "Brands", CONCATENATEX (
                    ALLSELECTED ( 'Product'[Brand] ),
                    Product[Brand],
                    ", "
                )
            )
        )
    ),
    Product[Color] IN { "Blue", "Green" },
    Product[Brand] IN { "Contoso", "Fabrikam" }
)

查询结果如下:

https://www.sqlbi.com/wp-content/uploads/AllSelected-Internals-06.png

关于迭代 Product[Brand]的讨论到此告一段落。现在考虑产品名称列,如果我们在对品牌的两个迭代中使用 ALLSELECTED (Product [Product]),应该期望得到什么结果?了解规则后很容易解决这个问题:ALLSELECTED 返回在最后一个影子筛选上下文中可见的所有产品名称。你可以在观察下面代码的时候猜一下答案:

EVALUATE
CALCULATETABLE (
    GENERATE (
        SELECTCOLUMNS (
            VALUES ( 'Product'[Brand] ),
            "Outer Brand", Product[Brand]
        ),
        GENERATE (
            SELECTCOLUMNS (
                ALL ( 'Product'[Brand] ),
                "Inner Brand", Product[Brand]
            ),
            ROW (
                "Products", CONCATENATEX (
                    ALLSELECTED ( 'Product'[Product] ),
                    Product[Product],
                    ", "
                )
            )
        )
    ),
    Product[Color] IN { "Blue", "Green" },
    Product[Brand] IN { "Contoso", "Fabrikam" }
)

答案非常直观:因为没有影子筛选上下文筛选产品名称,ALLSELECTED 返回了所有产品名称。

https://www.sqlbi.com/wp-content/uploads/AllSelected-Internals-07.png

基于这个例子,一个值得思考的好问题是:是否可以获得倒数第二层影子筛选上下文,就像在行上下文中使用 EARLIER 那样,如果只依赖 ALLSELECTED,答案是否定的。不过,DAX 不仅提供了 ALLSELECTED,它还提供了一种更强大的机制来处理外部上下文,无论是显式上下文还是影子上下文:它就是变量。如果在内部迭代中,想要访问品牌的外部上下文,只需要在内层的影子筛选上下文生效前,将 ALLSELECTED(Product[Brand])的结果保存在变量中,如下例所示:

EVALUATE
CALCULATETABLE (
    GENERATE (
        SELECTCOLUMNS (
            VALUES ( 'Product'[Brand] ),
            "Outer Brand", Product[Brand]
        ),
        VAR OuterProducts =
            ALLSELECTED ( 'Product'[Brand] )
        RETURN
            GENERATE (
                SELECTCOLUMNS (
                    ALL ( 'Product'[Brand] ),
                    "Inner Brand", Product[Brand]
                ),
                ROW (
                    "Brands", CONCATENATEX (
                        OuterProducts,
                        'Product'[Brand],
                        ", "
                    )
                )
            )
    ),
    Product[Color] IN { "Blue", "Green" },
    Product[Brand] IN { "Contoso", "Fabrikam" }
)

我们建议你不妨自己测试一下,结果只有 Contoso 和 Fabrikam 是可见的,这两个品牌来自外层筛选上下文。

https://www.sqlbi.com/wp-content/uploads/AllSelected-Internals-08.png

友情提示,使用变量使代码更容易编写和调试:建议你熟练掌握变量的用法,因为它们在复杂的 DAX 代码中扮演着非常重要的角色。

到目前为止,我们已经深入分析了 ALLSELECTED 使用单列作为参数时的语义。下一步我们增加复杂性,引用完整的表作为参数。

使用表作为参数

通过在公式中使用 ALLSELECTED(Product),情况将开始变得复杂。事实上,一个表包含多个列,在每一列上,都有可能存在通过 CALCULATE 设置的显式筛选器,或者迭代器设置的影子筛选器。此外,可以在每个列上嵌套多个筛选器(显式筛选器或影子筛选器),这使得事情更加复杂。让我们从一个完整的表开始学习 ALLSELECTED 的这种行为。

使用最简单的测试查询来说明 ALLSELECTED 用表或列作为参数时的第一个显著不同:

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

在本文的开头,我们已经声明 ALLSELECTED 忽略来自列的显式筛选器,它只使用影子筛选上下文,这适用于列作为参数的情况。但如果与表参数一起使用,此结论不成立,实际上,这个查询的结果是:

Helmet, Shoes, Keyboard, Piano

换句话说,使用表的 ALLSELECTED 接受 CALCULATETABLE 创建的显式筛选器,只返回颜色为蓝色或绿色、品牌是 Contoso 或 Fabrikam 的产品。你可能已经注意到,在第一个查询中,没有影子筛选上下文,只有显式的筛选上下文。

如果我们在其中一列上引入迭代会发生什么?下面这个查询显示了预期中的行为:

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

因为 ADDCOLUMNS 迭代的是所有产品颜色,所以现在有一个包含所有颜色的影子筛选上下文。这个上下文被 ALLSELECTED 激活,它将替换之前蓝色和绿色上的颜色筛选器。另一方面,品牌列没有被影子筛选上下文过滤,仍然在 Contoso 和 Fabrikam 上有一个显式筛选器。综合以上条件,查询结果是任何颜色的 Contoso 或 Fabrikam 的所有产品。换句话说,颜色上的筛选器被替换了,品牌筛选器则没有。我们建议读者查看原始数据,以充分理解这个复杂的结果。

https://www.sqlbi.com/wp-content/uploads/AllSelected-Internals-09.png

下一个查询比较复杂:外层的影子筛选上下文包含所有品牌,而内层影子上下文只包含两个品牌和颜色。因为 ALLSELECTED 激活的是每一列上的最后一个影子筛选上下文,所以它将在最内层的筛选上下文上关闭。事实上,它已经过滤了品牌和颜色。因此,在这种情况下,外层的影子筛选上下文没有被激活:

EVALUATE
CALCULATETABLE (
    GENERATE (
        SELECTCOLUMNS (
            ALL ( Product[Brand] ),
            "Outer Brand", Product[Brand]
        ),
        ADDCOLUMNS (
            CROSSJOIN (
                VALUES ( Product[Color] ),
                SELECTCOLUMNS (
                    VALUES ( Product[Brand] ),
                    "Inner Brand", Product[Brand]
                )
            ),
            "Products", CONCATENATEX (
                ALLSELECTED ( 'Product' ),
                Product[Product],
                ", "
            )
        )
    ),
    Product[Color] IN { "Blue", "Green" },
    Product[Brand] IN { "Contoso", "Fabrikam" }
)

产品列生成的内容对于结果的所有行都是相同的:

Helmet, Shoes, Keyboard and Piano

外层的 ALL ( Product[Brand] )没有被激活,被内层的影子筛选上下文覆盖

https://www.sqlbi.com/wp-content/uploads/AllSelected-Internals-11.png

初步总结

让我们总结一下目前为止的知识点。

本文隐藏内容查看价格为5G币,请先
单独购买的内容长期有效,不受时间限制(购买前先刷新当前页面)。加入VIP会员可享受全站权益,性价比更高。

到目前为止,我们提醒你注意这样一个事实:ALLSELECTED 经常被用作 CALCULATE 调节器,较少作为表函数使用。我们已经了解,下面这两个度量值会得到不同的结果:

AllSelectedWithCALCULATE :=
CALCULATE (
    CONCATENATEX (
        VALUES ( 'Product'[Product] ),
        Product[Product],
        ", "
    ),
    ALLSELECTED ( Product[Product] )
)


AllSelectedAsTableFunction :=
CALCULATE (
    CONCATENATEX (
        ALLSELECTED ( 'Product'[Product] ),
        Product[Product],
        ", "
    )
)

事实上,对于上面使用的第一个度量值,外部筛选上下文是由 VALUES 注入的,而不是 ALLSELECTED。此外,ALLSELECTED 的操作方式与 ALL 类似,后者在用作 CALCULATE 调节器时从筛选上下文中删除相应的筛选器。

截至目前,还有以下用法尚未分析:

  • 无参数的 ALLSELECTED 的用法:此时 ALLSELECTED 不再是一个表函数,而只是一个 CALCULATE 调节器。
  • ALLSELECTED 与上下文转换的计值顺序

无参数的 ALLSELECTED

在参数留空的情况下,ALLSELECTED 显然不是一个表函数。相反,此时它只能用作 CALCULATE 的筛选器参数。你可以这样直观地描述其行为:它在影子筛选上下文引用的所有列上执行 ALLSELECTED。在实际操作时,ALLSELECTED 恢复每个列的最后一个影子筛选上下文。

本文对类似行为已经做了深入探讨,就不再赘述了。我们将举一个例子让读者得出结论。观察下面两个度量值:

AllSel :=
CALCULATE (
    SUM ( Sales[Quantity] ),
    ALLSELECTED ()
)


AllSelSumX :=
SUMX (
    VALUES ( 'Product'[Brand] ),
    SUMX (
        VALUES ( 'Product'[Color] ),
        CALCULATE (
            SUM ( Sales[Quantity] ),
            ALLSELECTED ()
        )
    )
)

我们将其放入 POWER BI 报告中

https://www.sqlbi.com/wp-content/uploads/AllSelected-Internals-12.png

报告中只选择了 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 删除了由上下文转换生成的最后一个筛选上下文。不幸的是,这是一个错误的描述。这需要进一步解释:

本文隐藏内容查看价格为5G币,请先
单独购买的内容长期有效,不受时间限制(购买前先刷新当前页面)。加入VIP会员可享受全站权益,性价比更高。

总结

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

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

105
说点什么

1000
 
鼓掌微笑开心憧憬爱你色并不觉得吃瓜doge二哈喵喵思考笑哭捂脸悲伤大哭抓狂汗偷笑打脸捂眼黑线问号晕拜拜闭嘴衰咒骂ok作揖
26 评论数
83 被回复的评论
21 订阅评论的人数
 
查看最近回复
查看最热评论
  订阅本文评论  
最新 最旧 得票最多
提醒
成员
151****9937

高老师,截图里面提及的这是一个bug是指:
1.考虑了直接筛选是一个bug,即系统提供的ALLSELECTED函数有bug(也就是不应该考虑直接筛选);
2.还是ASELECTED应该考虑直接筛选,只是文中以下描述有bug:如果不存在影子筛选上下文,ALLSELECTED将返回列的所有值。

bug.png
成员
静石

AnotherSalesAmount :=SUMX (
CALCULATETABLE (
VALUES ( Product[Color] ),
Product[Color] = “Red”
),
SUM ( Sales[Quantity] )
)
在这种情况下,影子筛选上下文包含了一个对颜色的选择——即,只有红色。但是在迭代中,SUM (Sales[Quantity])仍然计算所有销售的总和。如果影子筛选上下文是活动(生效)的,那么引擎将只对红色产品的销售进行求和。
老师好,麻烦您能把最后这句话的函数写出来吗?我试着写了几个,都不对

成员
做一名学霸

EVALUATE
CALCULATETABLE (
ROW (
“Products”, CONCATENATEX ( ALLSELECTED ( ‘Product'[Product] ), Product[Product], “, ” )
),
Product[Color] IN { “Blue”, “Green” },
Product[Brand] IN { “Contoso”, “Fabrikam” }
)
实际上,ALLSELECTED 返回由最后一个影子过筛选上下文过滤的列值。你可能已经注意到,在调用 ALLSELECTED 时没有活动的迭代发生。所以没有需要激活的影子筛选上下文。因此,所有的产品名称都会被返回,因为颜色和品牌都不会直接筛选 Product [Product]列,它是通过交叉筛选被影响的。
这是我们学到的第一课:ALLSELECTED 不考虑筛选上下文。它的主要目的是检索之前设置的影子筛选上下文。ALLSELECTED 与 VALUES 有很大区别。VALUES 和 DISTINCT 总是会考虑筛选上下文,而 ALLSELECTED 不会。它在单个列上工作,并检查该列是否被影子筛选上下文过滤,忽略任何交叉筛选。

老师好,针对以上文中内容:
1.CONCATENATEX函数也是X结尾,是迭代函数吗?
2.如果CONCATENATEX函数是迭代函数,那应该创建了影子上下文呀?但文中说在调用 ALLSELECTED 时没有迭代发生,这是因为内嵌函数在其初始上下文中计值,所以对于ALLSELECTED函数来说没,迭代发生在外部CONCATENATEX,并没有发生在ALLSELECTED函数内部?是这样理解吗?
3. ALLSELECTED 不考虑筛选上下文。它的主要目的是检索之前设置的影子筛选上下文。所以当ALLSELECTED的参数为列时,它也不受CALCULATETABLE或者CALCULATE的筛选器影响吗?

成员
做一名学霸

老师好,所谓的影子上下文,是否就是迭代函数创建的行上下文? 捂脸

成员
做一名学霸

当你想把透视表的页面筛选器或切片器作为计算使用的参数时,ALLSELECTED 是一个非常有用的函数。
ALLSELECTED 只返回透视表在初始筛选上下文中的可见值。换句话说,ALLSELECTED 忽略透视表行和列上的筛选器,只考虑用于计算总计的筛选器。

老师好,我能不能这么理解ALLSELECTED函数:
1.我们自己可以先根据切片器、透视表中的筛选器等外部筛选器进行选中,然后ALLSELECTED函数会把我们选中的外部筛选器作为初始计值环境对表进行筛选,最后返回经过筛选的表。
2.当行标签、列标签这种外部筛选器包含在参数中表或者列中时,那么ALLSELECTED会忽略行标签、列标签的影响。。

成员
小本本

这里红框中的ALLSELECTED(Product)也是处理的Product的扩展表吗?如果Product和其他表存在双向关系的话, 这个结果会受不在Product扩展表中的其他表的列的双向筛选吗?

fdgdfg (小).png
成员
小本本

2022年8月这篇文章中介绍的原理微软更新了吗?

成员
暮色

大佬 请问下 这里的活动的迭代是啥意思

111.jpg
成员
烟花

高飞老师:

ALLSELECTED 既可以返回表,也可以移除筛选器并恢复之前的筛选上下文。这两种功能的实现,都是通过访问和使用迭代器在筛选上下文堆栈中留下的最后一个影子筛选上下文实现的。恢复之前的筛选上下文和结尾的最后一个影子筛选上下文是一回事,因为名称不一样,有点分不清 大哭

再者:
当ALLSELECTED 参数为列时,如果影响参数列筛选条件有两个,一个显式筛选上下文一个影子筛选上下文(被激活),也就是ALLSELECTED 参数列的筛选上下文堆栈中有两个筛选上下文,因为同时影响参数列,后执行的筛选上下文会覆盖前面的筛选上下文,当显式筛选上下文位于筛选上下文堆栈的顶层,影子筛选上下文位于上下文堆栈的底层,最终影响ALLSELECTED 参数列的筛选上下文是位于筛选上下文堆栈顶层显式筛选上下文,还是为底层的影响筛选上下文?

成员
lyliuyouyang

高飞老师你好,我在看这个allselected函数的时候,因为不好理解,所以也跟着文章中的案例在做,但是有个问题出现了,就是使用变量和不使用变量发现返回的结果不一样。
table1使用了变量,返回的是图片2的结果,感觉就是values传递的影子上下文并没有传递给t1,这个结果不是预期的。但是不使用变量就和案例中的结果是一样的,请问这是什么原因呢。

table1.png
屏幕截图 2022-03-08 082620.png
游客
Jack Sang

这篇文章有个错误:
ALLSELECTED把列作为参数时候,是考虑施加到该列的直接筛选的.

例如: 下面的查询返回Bike
EVALUATE
CALCULATETABLE (
ROW (
“Products”, CONCATENATEX ( ALLSELECTED ( ‘Product'[Product] ), Product[Product], “, ” )
),
‘Product'[Product] = “Bike”
)

成员
CatCatLa

老师你好,创建计算表代码(相当于查询)如下: test_tb_allselected = VAR Brands = FILTER ( ALL ( ‘Product'[BrandName] ), ‘Product'[BrandName] IN {“… 阅读更多 »

q01.jpg
成员
xifeng

老师,当筛选器卡里存在使用自定义的度量值作为矩阵视觉对象的筛选器时,且矩阵的行标签有多列,那么此时的ALLSELECTED(某列行标签)应返回什么,简单来说就是这种情况下的影子筛选器有哪些?为何下图中的产品大类返回的值有三个也有两个?

2021-03-14_122742.png
游客
19817253071

allselected表函数(设定为B对象) ,嵌入迭代器函数里,激活迭代表(设定为A对象)与迭代器作用产生的影子上下文。A对象是产生行上下文的载体。而B对象是行上下文的接受者,因为有行上下文的影响,所以ALLSELECTED 表函数才激活此迭代器与迭代表载体产生的影子上下文。换句话讲,allselected 表函数在迭代器函数里,是行上下文的使用者,如果没有获得接受行上下文的这个位置(即合适的参数位置),那么影子上下文无从被激发即无法被感知被调用。这个意思最后要解释的是,allselected 表函数如果做为迭代器函数的第一参数,它的作用是迭代表,即A对象。它是行上下文的载体。它无法也不能自己激发自己行上下文迭代的影子上下文。最后,在这种情况下,allselected 表函数调用的影子上下文,只好跳出这层迭代函数,向外找。或者是外部筛选上下文对其参列或参表的直接筛选,或者是外部嵌套的迭代函数里的第一参数是否是allselected的列参或表参。

游客
19817253071

concatenatex 是迭代器么。

成员
xifeng

老师,对于文中总结的ALLSELECTED的行为,好像不能解释下图中的例子啊,下面的是文中的描述:
————————–
迭代器创建影子筛选上下文
CALCULATE 创建显式筛选上下文
ALLSELECTED 是一个表函数,它在与表或列一起使用时返回不同的结果。
使用列作为参数,ALLSELECTED 返回最后一个影子筛选器包含的列值(如果有的话)。如果不存在影子筛选上下文,ALLSELECTED 将返回列的所有值。
使用表作为参数,ALLSELECTED 返回一个表,其中包含对具有影子筛选器的任何列应用最后一个影子筛选器后得到的所有行;或者,如果没有可用的影子筛选上下文,ALLSELECTED 返回最后一个显式筛选上下文。
————————–

我的疑问:

1、FILTER属于迭代函数,能不能创建影子筛选上下文?如果能,为何下图中含有FILTER的查询只返回筛选后的值?如果不能,那么下图中含有FILTER的那个查询由于不存在影子筛选上下文,ALLSELECTED使用列作为参数,应该返回列的所有值才对吧?

2、在显式筛选器内部里的迭代器会不会创建影子筛选上下文?

3、CALCULATE和CALCULATETABLE的筛选器参数如果使用布尔表达式,其等价形式是FILTER(ALL(),filterExpression),会不会创建影子筛选上下文?

我的理解:

1、ALLSELECTED作为表函数时,将激活参数所指定列或表的每一列在对应列上的最后一个影子筛选上下文,然后因为影子筛选器覆盖了对应列的显示筛选器(如果有的话),才使ALLSELECTED 返回最后一个影子筛选器包含的列值,不考虑交叉筛选。;若参数所指定列或表的每一列在对应列上不存在影子筛选上下文,那么将返回该列被最后一个显式筛选器筛选后的值,不考虑交叉筛选。

2、ALLSELECTED作为筛选调节器时,将恢复参数所指定列或表的最后一个影子筛选上下文,如果不存在影子筛选上下文则不执行任何操作。

上面是我对ALLSELECTED的一些疑问与理解,希望老师帮我解惑并看看我的理解正不正确,谢谢 爱你

problem4.jpg
sss.jpg
游客
Lily

老师,一张事实表:订单表里面有商品名称列,商品分类列,销售月份列,省市列,金额列,另有维度表:省市,维度表:商品分类,两张维度表分别和事实表建立一对多关系,商品分类和省市为切片器,假设切片器单选了一个商品分类/咖啡,另切片了两个省市/北京和上海,如何用dax写出不重复的订单商品表?all(’ 订单表’[商品名称])?还是allselected (’ 订单表’[商品名称])?
我测试了下,这两个函数返回的都是全部不重复商品,而不是分类为咖啡,城市为北京和上海的不重复商品。怎样才能返回分类为咖啡,城市为北京和上海的不重复商品呢?谢谢老师啦

成员
xifeng

老师,我看完文章后回过头去理解《初始ALLSELECTED》里的三个例子,然后有点不太明白,当ALLSELECTED作为调节器时,其运作方式是清除筛选器还是说通过返回表来作为筛选器?例如,在第一个公式中,使用了列作为ALLSELECTED的参数,此时没有迭代也就是没有影子筛选上下文:
(1)如果是返回表的话,按照文章中的意思(ALLSELECTED 返回最后一个影子筛选器包含的列值(如果有的话)。如果不存在影子筛选上下文,ALLSELECTED 将返回列的所有值),应该是返回occupation列的所有值,相当于CALCULATETABLE(ALL(customer[occupation])),但是看结果却仅仅返回了被选中的manual和professional
(2)如果其运作方式是清除筛选器,那么又是如何运作的呢,在文章中好像没有提到
希望老师可以解惑,谢谢!

0_1.png
成员
与你有关

老师,常常我们通过建透视表来创造行上下文。在power bi工作中,什么时候会需要 建立查询。比如说用到函数 values 来创建查询,是在需要建一些根据我们具体需求的计算表的时候吗?

DAX 圣经

导读

初识 DAX

DAX 基础知识

DAX 原理

DAX 高级原理

基础函数类型

迭代函数

CALCULATE 函数

CALCULATE 调节器

基础表函数

条件判断函数

查找匹配函数

时间智能函数

统计类函数

投影函数

分组/连接函数

集合函数

其他函数