ALLSELECTED 是最复杂的 DAX 函数,本文完整的介绍了它的复杂性。但是,对于绝大部分用户,你可以不必深究这背后的原理,理解初识 ALLSELECTED 部分可以帮助你解决大部分问题
初识 ALLSELECTED
当你想把透视表的页面筛选器或切片器作为计算使用的参数时,ALLSELECTED 是一个非常有用的函数。例如,假设你希望构建如图所示的报告。
在这个报告中,显示了当前行的销售额占列总计的比重。这个百分比难以计算的原因是,产品颜色被同时用作切片器(在这个例子中,我们只选择了部分颜色)和行标签。如果你借助到目前为止获得的知识,可以试试这个公式:
[SalesPct] := DIVIDE ( [Sales Amount], CALCULATE ( [Sales Amount], ALL ( Product[Color] ) ) )
使用 ALL (Product[Color])从颜色列中移除了筛选器,并尝试在列级别计算总数。不幸的是,ALL 移除了来自行和切片器的所有筛选,这将导致一个错误的结果。你可以从下面的数据透视表中看到公式的结果,其中总计不显示 100%,而是一个较小的值。
这里的问题是计算分母时使用了所有颜色的数据,即使用户只在切片器中选择了其中一部分颜色。对于每一行,计算时使用的分母要大于透视表实际显示的总数。
我们需要的是一个不返回所有颜色,但可以返回初始筛选上下文中所有选择颜色的函数,即一个完整的透视表。我们将这种计算称为视觉总计(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 是有意为之:
WrongSalesQuantity := SUMX ( VALUES ( Product[Color] ), SUM ( Sales[Quantity] ) )
在迭代过程中,颜色列的行上下文不会转换为筛选上下文,因为没有 CALCULATE 执行上下文转换。正如我们介绍的,这里确实存在一个筛选上下文:“影子”筛选上下文。影子筛选上下文包括迭代器启动时当前筛选上下文中活动的颜色列表。实际上,影子筛选上下文在迭代过程中是不活动的,除非它被一个叫做 ALLSELECTED 的函数激活。换句话说,当不使用 ALLSELECTED 时,忽略影子筛选上下文的存在是安全的。另一方面,当你决定使用 ALLSELECTED 时,影子筛选上下文变得至关重要。
细心的读者可能会注意到,上面的公式中即使影子筛选上下文是活动的,它也不会改变结果。事实上,由于影子筛选上下文包含了所有颜色,所以结果与初始筛选上下文相同。但是,随着下面这个表达式的出现,复杂性开始增加:
AnotherSalesQuantity := 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 的代码片段来分析其行为。
使用列作为参数
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]列,它是通过交叉筛选被影响的。
用下面这种方式改写前面的公式可能让你感到困惑:
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。
初步来看,似乎 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" } )
这一次,结果将是一个三行表,每一行显示所有品牌:
原因在于 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 包含两个品牌:
交换内部和外部品牌列使用的 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" } )
查询结果如下:
关于迭代 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 返回了所有产品名称。
基于这个例子,一个值得思考的好问题是:是否可以获得倒数第二层影子筛选上下文,就像在行上下文中使用 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 是可见的,这两个品牌来自外层筛选上下文。
友情提示,使用变量使代码更容易编写和调试:建议你熟练掌握变量的用法,因为它们在复杂的 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 的所有产品。换句话说,颜色上的筛选器被替换了,品牌筛选器则没有。我们建议读者查看原始数据,以充分理解这个复杂的结果。
下一个查询比较复杂:外层的影子筛选上下文包含所有品牌,而内层影子上下文只包含两个品牌和颜色。因为 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] )没有被激活,被内层的影子筛选上下文覆盖
初步总结
让我们总结一下目前为止的知识点。
到目前为止,我们提醒你注意这样一个事实: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 报告中
报告中只选择了 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 的行为才更容易理解。也就是说,因为影子筛选上下文的存在,ALLSELECTED 变成了一个非常复杂的函数,而且它与显式筛选上下文的交互使得完整描述计值过程变得困难。
对于 ALLSELECTED 的使用建议是,当且仅当没有迭代发生时,使用 ALLSELECTED 来检索查询上下文,也就是透视表或报表所在的计值环境的上下文。如果有任何迭代发生,建议避免使用 ALLSELECTED,因为结果几乎是不可预测的,并且也是非常复杂和难以理解的。本文介绍了理解 ALLSELECTED 行为所需的所有工具。但是,我们不希望每次需要调试度量值时都必须执行所有这些复杂的推理步骤。因此,这个建议始终成立:ALLSELECTED 不应该在迭代中使用,除非用户已经非常清楚他们在做什么,并且有强烈的需求。在大多数情况下,变量可以避免在迭代中使用 ALLSELECTED。我们强烈建议你使用变量。
老师 ALLSELECTED 仅做 CALCULATE 调节器,还有那么复杂的逻辑吗
我发现无法忽略筛选器
高老师,您好!又要麻烦您给看看!
《DAX权威指南》书中第442~443页 (图14=17 ALLSELECTED和KEEPFILTERS 一起使用产生另一种结果,包含许多空行)中,对生成的结果这样描述:“在这个查询中,ALL返回所有品牌,而影子筛选上下文也包含了所有品牌。”
但就我截图中的查询结果看,我感觉影子筛选上下文并非包含了所有品牌,而是只包含了CALCULATETABLE中的Brands变量筛选器所包含的6个品牌。
不知道是不是我哪里又理解错了,麻烦老师解惑,谢谢高老师!
高老师好:
这个地方的公式是不是少了?
SUM(Sales[Quantity]) * SUM(Sales[Net Price])
高老师,截图里面提及的这是一个bug是指:
1.考虑了直接筛选是一个bug,即系统提供的ALLSELECTED函数有bug(也就是不应该考虑直接筛选);
2.还是ASELECTED应该考虑直接筛选,只是文中以下描述有bug:如果不存在影子筛选上下文,ALLSELECTED将返回列的所有值。
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扩展表中的其他表的列的双向筛选吗?
外部筛选条件覆盖不会覆盖CALCULATE内部的筛选器;筛选器参数本身也不会产生新的筛选器,所以你的表述是错误的。
第一个度量值的计算过程在正文中已经做过介绍。
2022年8月这篇文章中介绍的原理微软更新了吗?
大佬 请问下 这里的活动的迭代是啥意思
高飞老师:
ALLSELECTED 既可以返回表,也可以移除筛选器并恢复之前的筛选上下文。这两种功能的实现,都是通过访问和使用迭代器在筛选上下文堆栈中留下的最后一个影子筛选上下文实现的。恢复之前的筛选上下文和结尾的最后一个影子筛选上下文是一回事,因为名称不一样,有点分不清
再者:
当ALLSELECTED 参数为列时,如果影响参数列筛选条件有两个,一个显式筛选上下文一个影子筛选上下文(被激活),也就是ALLSELECTED 参数列的筛选上下文堆栈中有两个筛选上下文,因为同时影响参数列,后执行的筛选上下文会覆盖前面的筛选上下文,当显式筛选上下文位于筛选上下文堆栈的顶层,影子筛选上下文位于上下文堆栈的底层,最终影响ALLSELECTED 参数列的筛选上下文是位于筛选上下文堆栈顶层显式筛选上下文,还是为底层的影响筛选上下文?
高飞老师你好,我在看这个allselected函数的时候,因为不好理解,所以也跟着文章中的案例在做,但是有个问题出现了,就是使用变量和不使用变量发现返回的结果不一样。
table1使用了变量,返回的是图片2的结果,感觉就是values传递的影子上下文并没有传递给t1,这个结果不是预期的。但是不使用变量就和案例中的结果是一样的,请问这是什么原因呢。
这篇文章有个错误:
ALLSELECTED把列作为参数时候,是考虑施加到该列的直接筛选的.
例如: 下面的查询返回Bike
EVALUATE
CALCULATETABLE (
ROW (
“Products”, CONCATENATEX ( ALLSELECTED ( ‘Product'[Product] ), Product[Product], “, ” )
),
‘Product'[Product] = “Bike”
)
老师你好,创建计算表代码(相当于查询)如下: test_tb_allselected = VAR Brands = FILTER ( ALL ( ‘Product'[BrandName] ), ‘Product'[BrandName] IN {“… 阅读更多 »
老师,当筛选器卡里存在使用自定义的度量值作为矩阵视觉对象的筛选器时,且矩阵的行标签有多列,那么此时的ALLSELECTED(某列行标签)应返回什么,简单来说就是这种情况下的影子筛选器有哪些?为何下图中的产品大类返回的值有三个也有两个?
allselected表函数(设定为B对象) ,嵌入迭代器函数里,激活迭代表(设定为A对象)与迭代器作用产生的影子上下文。A对象是产生行上下文的载体。而B对象是行上下文的接受者,因为有行上下文的影响,所以ALLSELECTED 表函数才激活此迭代器与迭代表载体产生的影子上下文。换句话讲,allselected 表函数在迭代器函数里,是行上下文的使用者,如果没有获得接受行上下文的这个位置(即合适的参数位置),那么影子上下文无从被激发即无法被感知被调用。这个意思最后要解释的是,allselected 表函数如果做为迭代器函数的第一参数,它的作用是迭代表,即A对象。它是行上下文的载体。它无法也不能自己激发自己行上下文迭代的影子上下文。最后,在这种情况下,allselected 表函数调用的影子上下文,只好跳出这层迭代函数,向外找。或者是外部筛选上下文对其参列或参表的直接筛选,或者是外部嵌套的迭代函数里的第一参数是否是allselected的列参或表参。
concatenatex 是迭代器么。
老师,对于文中总结的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的一些疑问与理解,希望老师帮我解惑并看看我的理解正不正确,谢谢