[PowerBI]ALLEXCEPT在扩展表中表现错误

shwangfeng · 发表于 2019-5-18 22:26

现有简单模型Data表与其日历，日历由CALENDARAUTO自动生成(并添加自定义列YearMonth)，Data形如：

求：每个Name对应的YearMonth个数。
写法如下：

Wrong =
ADDCOLUMNS (
VALUES ( Data[Name] ),
"MonthNum", CALCULATE (
CALCULATE (
DISTINCTCOUNT ( 'Date'[YearMonth] ),
CALCULATETABLE ( ALLEXCEPT ( Data, Data[Name] ) )
)
)
)

复制代码

我的理解：外层Calculate将当前Name转换为筛选上下文，内层Calculatetable在Allexcept作用下保留扩展表Data的Name列筛选，同时清除其他列筛选，即最后扩展表Data只有Name列有筛选，又由于Data扩展表包括日历表，因此实现对日历表的筛选，计算预期值。
实际：所有行都是一个值，即全部的YearMonth个数，Name列筛选无效。
同样的思路，改用All可行：

Correct =
ADDCOLUMNS (
VALUES ( Data[Name] ),
"MonthNum", CALCULATE (
DISTINCTCOUNT ( 'Date'[YearMonth] ),
FILTER ( ALL(Data),Data[Name]=EARLIER(Data[Name]) )
)
)

复制代码

不能理解，求解答。

pbix附件：

Test.zip (63.06 KB, 下载次数: 31)

shwangfeng · 发表于 2019-5-21 14:32

本帖最后由 shwangfeng 于 2019-5-21 14:33 编辑

原因虽然不晓得，但其实改正的办法非常简单，只要在Calculatetable后面加上原表名就好了：

Wrong =
ADDCOLUMNS (
VALUES ( Data[Name] ),
"MonthNum", CALCULATE (
CALCULATE (
DISTINCTCOUNT ( 'Date'[YearMonth] ),
CALCULATETABLE ( Data, ALLEXCEPT ( Data, Data[Name] ) )
)
)
)

复制代码

但这也只能从侧面说明Allexcept并非像《DAX圣经》或者dax.guide中说的那样跟All差不多，仍然没有揭示本质原因，即到底它返回的是不是表。
这两种官方参考每次提到Allexcept都只说它可以用扩展表中的任何表或者列作为排除参数，而从来不进一步展示这样做的结果是不是符合他们下的定义，看得我有点窝火。

zjnfxxh · 发表于 2019-5-21 16:30

CALCULATETABLE ( ALLEXCEPT ( Data, Data[Name] )，
我感觉是：除了name列，其余列均去除了筛选。这一点是肯定的。
但根据扩展表的概念，data表扩展版本也包含了日期表，因此“其余列”包含了日期表的各个列。日期表的各个列均 “ALL” 了，即不筛选，导致计算的年月个数均是相同的。

mathspirt · 发表于 2019-5-21 17:30

楼上正解，用事实表做筛选条件一定要注意对扩展表的影响，DAX黄金法则之一，能用列做筛选的不要用表做筛选。www.sqlbi.com上对此有专门的讲解，原理跟你这个错误一样。

shwangfeng · 发表于 2019-5-21 18:07

zjnfxxh 发表于 2019-5-21 16:30
CALCULATETABLE ( ALLEXCEPT ( Data, Data[Name] )，
我感觉是：除了name列，其余列均去除了筛选。这一点 ...

感谢回答！
我对扩展表的理解也是这样，而且正因为Data的扩展表包含了Date，所以在保留Name筛选的情况下可以计算每个Name对应的非重复YearMonth个数，无论是书上还是网站上都举过相应的例子，没有问题。也就是说，整个扩展表中"只有Name列有筛选"正是计算出正确结果的前提——参考我改正后的版本，是可以返回预期结果的。
要计算出正确结果，大体的计算流程推理如下：
Addcolumns按Name制造行上下文，对于每个迭代到的Name，使用Calculate将当前Name转换为筛选上下文，但因为上下文转换只会作用于Calculate第一参数，而此处Name列属于单列筛选，单列筛选只对包含它的扩展表有效，Date表的扩展表不包含Name，所以这里的转换实际不能对结果产生影响。为了真正激活Name列筛选，必须在Calculate第二参数以后，再构建一个包含Date表的扩展表，即此处的Data表，如果这个Data的扩展表中包含Name列筛选，那么它就能覆盖（因为上下文转换先发生）之前（其实无效的）上下文转换效果，返回预期结果。所以真正的问题在于，如何构建这个符合需要的“既转换并保留Name列筛选，同时清除其他所有筛选”的Data扩展表。

因为仍然要进行上下文转换，并且返回结果需要一个表，所以嵌套了一个Calculatetable，它将Data扩展表作为第一参数，在第二参数中使用Allexcept清除扩展表中其他列的筛选，同时进行上下文转换（因为外层Calculate做的转换并不影响第二及以后的参数，所以此时行上下文还在，可以再被转换）作用于第一参数，这样两部分组合以后就是想要的结果了，最终结果符合预期：

ADDCOLUMNS (
VALUES ( Data[Name] ),
"MonthNum", CALCULATE (
DISTINCTCOUNT ( 'Date'[YearMonth] ),
CALCULATETABLE ( Data, ALLEXCEPT ( Data, Data[Name] ) )
)
)

复制代码

而错误的版本仅仅是删除了其中的Data,部分，此时Allexcept成为第一参数，接受上下文转换，又因为这个转换正好就是Allexcept排除掉的Name列，所以Calculatetable返回的Data扩展表理应和前面正确的算法相同，进而最终结果也相同。

而现在不是，说明Calculatetable并没有返回预期的扩展表，所以问题就在Allexcept上面，它到底返回的是什么？
按道理说Calculatetable处理过的总应该是一个表，难道这个表不是扩展表吗？

我的感觉是Allexcept并不是简单的返回表，一般筛选条件只有单列和整表两种，但Allexcept返回结果既不是单列也不是整表，可能是什么特别的机制——也许它实际返回的是由很多单列组成的组合，而Date扩展表不包含Name列，所以单列筛选再次失败？
而且这样也解释了为什么这个问题只出现在扩展表中，如果是普通的计算（例如同一表内）就没有问题。
只不过我一点证据也没有，而且我也无法理解Calculatetable到底是怎么对待返回值的，毕竟错误版本也是经过它处理的。

shwangfeng · 发表于 2019-5-21 18:42

mathspirt 发表于 2019-5-21 17:30
楼上正解，用事实表做筛选条件一定要注意对扩展表的影响，DAX黄金法则之一，能用列做筛选的不要用表做筛选 ...

我是想知道这个函数的原理，黄金法则我在sqlbi上面也看到过，但我遇到的错误和他们举的例子不同，找不到相关的解释。

张文洲 · 发表于 2019-5-22 00:06

本帖最后由张文洲于 2019-5-22 08:06 编辑

shwangfeng 发表于 2019-5-21 18:42
我是想知道这个函数的原理，黄金法则我在sqlbi上面也看到过，但我遇到的错误和他们举的例子不同，找不到 ...

是个典型的问题，涉及到ALLEXCEPT函数的用法，不过具体原理说起来确实有些复杂

shwangfeng · 发表于 2019-5-22 07:59

张文洲发表于 2019-5-22 00:06
是个典型的问题，涉及到ALLSELECTED函数的用法，不过具体原理说起来确实有些复杂

大佬是说，只有看明白号称最复杂的Allselected和什么shadow context才能彻底搞清楚这个问题咯？可是每一种参考资料提到Allexcept都只是简单地说它是all的小弟，根本没有展开……既然如此，我先去研究一下Allselected看看。谢谢指点。

张文洲 · 发表于 2019-5-22 08:06

shwangfeng 发表于 2019-5-22 07:59
大佬是说，只有看明白号称最复杂的Allselected和什么shadow context才能彻底搞清楚这个问题咯？可是每一 ...

是ALLEXCEPT，我写错了

shwangfeng · 发表于 2019-5-22 09:56

张文洲发表于 2019-5-22 08:06
是ALLEXCEPT，我写错了

我主要就是想知道这个函数的原理，它如何接受外部筛选（包括第一参数以及后面的排除参数），以及它返回的是表还是列，我在五楼写了我的想法，请有空的时候指点其中的错误，感激不尽。

		自动登录	找回密码
密码			免费注册

[求助] [PowerBI]ALLEXCEPT在扩展表中表现错误

评分

评分

评分