关于 “按列排序” 两个列的“坑”问题

Power零售数据BI 整理

2018.5.17

      依据SQLBI最新的一篇文章，我找翻译软件翻译了一下，稍做修改，能大致看懂就行。然后，再作了点进一步的解释。并附带了解RANKS函数在此问题下的举例。
      感谢官方文献。 以下是原文的简体版（可能与原文有出于）：

      标题：当“按列排序”列处于活动状态时，在Power BI中需要删除该列上存在的筛选器。

        Power BI用户在编写DAX代码时，一个经常遇到的问题是：当某个被指定为’按列排序”后的列处于活动状态时，删除该列的过滤器（比如使用ALL函数）可能会有意想不到的结果。 起先，在一篇与RANKX有关的文章中谈到了这个话题，但在那里，该问题并没有做过多介绍。所以，我认为最好是有一个专门的博客文章来说明它。事情是这样的：假如有一下这样的公式：

% ofYearWrong =DIVIDE(
[Sales Amount],
CALCULATE([Sales[Amount],
ALL('Date'[Month Name])))

          如图，这就产生了对下面透视表中年度百分比的错误计算:

      原因是：[Month Name]列的属性是按[Month]“按序排列”后的列 ——也就是透视表的行值。这样做，避免四月作为一年中的第一个月（如果以原数据的默认方式，将是按字母排列，四月应该在最前面）。
      如果使用Excel作为输出，这不是问题。但在Power BI中，必须修改[Month Name]列上过滤器的筛选条件，即在ALL函数中应同时包括[Month ]列。

这是正确的公式：

% of Year Correct = DIVIDE(
[Sales Amount],
CALCULATE( [Sales Amount],
ALL('Date'[Month Name],'Date'[Month])))

        原文中使用了  SUMMARIZECOLUMNS（）来验证，大致的意思是说：在透视表里将[Month ]列放置在行时，相当于使用SUMMARIZECOLUMNS（）在该列上做分组行（处理成唯一值的行，然后将当前行排序后计算）处理，即相当于作了排序动作。但是，由于时期表里存在[Month]列，该列是[Month Name]列指定为按序依据的原列表。即[Month Name]列在数据模型里存在指定的排序列。如下图：

      这时候，无论以哪种排序方式处理该列后，即相当于SUMMARIZECOLUMNS（）作用在两个列上，需要同时使用ALL（）来删除这两个列表上的筛选。即修改为原来的第二个公式：

% of Year Correct = DIVIDE (
[Sales Amount],
CALCULATE ([SalesAmount],
ALL ( 'Date'[MonthName], 'Date'[Month] ) ))

        请注意：在Excel中不具有此问题, 因为它依赖于 MDX, 并不更改查询的语义, 并自动将自然顺序应用于该字段列。不需要关注这一点。

    其实，只需要记住一句话，也就是原文里的结论：
    经验法则是：当你需要从某个“按列排序”列中移除筛选器时，它的列属性可能被引用到另一个列，应该在ALL语句中同时包含该列——否则，ALL函数在计算中可能不会达到预期的效果。

      其实问题已经结束，但原因呢？好像并没有讲清。也就是，我们再加了个问题：

为什么需要这样处理呢？

      文章中提到RANKS示例，我们找到这篇文章，然后也放在这里，作统一的一次性说明：并将它处理成一个普遍的问题？ 试试也是可以的。

一、我们做第一次、第一步的了解：

      模拟一个数据模型表，包含三列，分别是：Values、Name、State，其中Name列为唯一值列表。

      我们使用[ Sum Values]度量来对[Name]列执行排序：

结果显示：

        这里创建的是一个度量值, 而不是使用计算列（有利于本例说明）。因为希望根据当前筛选器显示前三名 (例如,，可以筛选状态列实现)，利用 "Power BI" 中的表和行列隐藏在所有度量值显示中返回空白的行的事实记录,，使用如下度量值获得所需的结果:

    [Top 3 Cases] := IF(  [Rank Name Simple]<= 3,  [SumValue]）

获得结果:

      但是, 注意到透视表中的度量值并不是按希望的大小自然顺序排列的。本例中, 因为只有三列，比较简单，将适当的自然顺序直接应用于报表中的3列了，但在更复杂的情况下,，要求可能会有所不同,，自然顺序需要由表中的另一列来定义它。
        在此示例中, 我们希望通过使用同一表里的"Value" 列以升序方式对 Name 列进行排序。因此, 我们将 "按列排序" 应用于 " Name " 列。方法见前面的图示。  这时，结果显示了所有的Name, 而不仅仅是前3个：

    为了更好观察这种行为的原因, 我们也同时显示Rank Name Simple度量：

        这说明，"按列排序" 设置似乎会破坏 RANKX 的行为（行的行为）。这似乎是一个 bug，事实并非如此。Rank Name Simple度量值由两个列所迭代：列表计算的Name 和Value列，这两列分别在所创建的行筛选中计算。无论应用于表的列是哪一个，都将发生这种情况。当然，我们在度量计算之前，在报告中使用的是其中的[Name ]列。

      因此， 当在报表中包含“按列排序”中的其中一列时，则 "PowerBI" 也会在同一DAX 查询中包含相应的另一个排序列。对于任何聚合来说，这通常不是问题，因为这两个列具有相同的粒度级别（月级别）。
        但是，当在报表中包含的列 (示例中的Name列) 上定义了一个迭代器（行筛选）时, 以及在这样的迭代器 (比如RANKX 函数)发生的当前筛选转换 (度量[Sum Value])行中附加有排序列 (Value) 的存在时，计算度量值的当前筛选将生成一个列表筛选，其中包含对由 RANKX 迭代的所有行的相同筛选器。
      在将 RANKX 应用于其中一个列时，可以通过将 "排序" 列包括在内来避免此问题。例如，可以编写:

[Rank Name] := IF(HASONEVALUE(Test[Name] ),
                        RANKX(ALL( Test[Name], Test[Value] ), [Sum Value]))

      通过这种方式，确保 RANKX 中的[Sum Value]度量生成的筛选转换，覆盖由Power BI查询中生成的Name和Value列的列表筛选。 同时删除所有不必要的度量，这将正确地显示出前三名：

        这里，唯一的问题是，只有Total总计并不是针对可见行求和 (结果应该是240)，这是因为最初的[Top 3 Cases]度量不检查单个[Name]列的选择。如果需要删除这样的总计, 可使用以下度量值 (如果是使用较大数据，受性能影响，建议最好去掉可见行的总计)。

[Top 3Cases]:=    IF(HASONEVALUE(Test[Name] ),    IF(  [Rank Name Simple]<=3,[SumValue]  ))

      如果使用 BI, 请记住, 对数据模型应用 "按列排序" 条件可能会破坏现有的度量值。应该知道，在物理表上的迭代器不应受此问题的影响，但表的粒度可能不是需要的正确计算，而且从性能角度来看，迭代表时的列表转换可能更昂贵。
        在使用迭代器在一个或多个列上生成 DAX 度量值之前，我们可以考虑在数据模型中设置“按列排序”，这是最佳做法。应用 "按列排序" 设置时，应验证该列是否在某些迭代器中被使用，以及这是否会影响该度量值的计算。

      说真的，我每次看这些筛选转换、更改之类的文章，总有一种似懂非懂的感觉，一种言犹未尽的感觉……。那么，让我们试着继续。

      二、我们做第二次、进一步的了解：

      1、其实这个案例比较特别。也算是列表关系里的一个“坑”。
      我们知道，“按列排序”只可能发生在同一个模型表里，即使该表中唯一值最大的列表有时不止一个，但作为活动物理关系列的只有一个。上例中因为只使用了简单的三个列，如果是复杂点的更多列表的数据模型，比如可能真正的关系列表是在公式中并没有出现的[Date]列。因此，我们说：在这个表里，其他列都是通过[Date]连接为数据模型列表（组成本身所在的这个表或与其他表的列表连接成扩展表）。 
        按理说，[Month Name] 与[Month ]都不是该表的关系列表，只能被动的被关系列表分别引用。而且关系不能同时作用于这两个列上。它们之间也应该互不影响才对。问题的关键只可能出现在“按列排序”上。
      第一个公式中，我们通过ALL( )去掉了[Month Name]列表上所有关系（因为要将它放置在透视表的行里，并希望不受该列上所有其他筛选器影响，而计算当前月的度量），按照正常思路，这应该不会计算错误，但结果出于我们的意料。

      2、这时候我们通常的想法都是检查公式有没有错误。问题来了，天空飘下四个字：如何检查？
        其实，通过前面官方的介绍，“按列排序”的原理应该是列表结构的一部分：即在计算中成为了关系列表。我们马上想到：能够转换列表结构供内部引擎计算的只有CALCULATE函数（以及CALCULATEDTABIE函数）。
        前面我们讨论过：任何一个CALCULATE ( ) 其实都是引擎执行下的一个附加（虚拟）列表模型，是提供给内部引擎计算的列表结构。CALCULATE ( ) 的结果错了，首先要检查的就是这个计算需要的列表结构！！它包括列表关系以及列表属性层次结构等。由于列表关系本身就是一种数据结构，这里我们检查的就是该列表关系。
      因此，换句话说： 任何DAX计算错误，首先检查的总是列表关系！(很多人总是从检查自已的公式条件入手，而忽说了关系的检查)。

      而且，应该记住：有时候，我们是需要某个列表关系来计算，反过来，有时候恰恰是不需要某个列表关系来计算（例如ALL系列：去掉列表关系，透视表里希望不显示当前没有度量计算的行等）。
      本例中，我们使用ALL([Month Name] )去掉了该列表上所有关系，但是，该关系结构表里还有一个具备相同唯一值（基数）的另一个列[Month]（而且同粒度），因为具有相同基数，引擎内部通过关系列表找到这两列中的任何一列来构建计算列表都是一样的结果。也就是说，ALL([Month Name])之后，关系列表（比如[Date]列）通过关系传递，会找到[Month]列来构建相同的关系列表来参与计算，效果是一样的！
        这时候，你可以理解为：第一个公式里，通过ALL([Month Name])显式处理了该列表的关系后，还存在一个由[Month]列接管的、能产生同样效果的隐式关系。所以必须要一同去掉（有可能还有其他这样的列）。
      当然，所有这一切都发生在当前的计算中，可理解它为一种隐式虚拟关系，“按列排序”的两个列并不存在实际意义上的物理关系，所以，在物理表里的计算并不会受影响。
      理解了以上这些，如是就有了前面的第二个正确的公式：

% of Year Correct =DIVIDE (
[Sales Amount],
CALCULATE ([SalesAmount],
ALL ( 'Date'[MonthName], 'Date'[Month] ) ))

  3、结论

    （1）当计算中使用了“按列排序”的两个列中任何一个列时，可能会出现隐式的虚拟关系； 

      （2）再次重复之前说过的一句话：很多时候，我们可以以某种方式或某个自己能把握的概念来达到理解DAX的目的（官方也推出过很多通俗易懂的概念），但DAX最终的内部核心却一直是一个专业的领域。一句话，你可以用这样或那样的方式方法去理解它，但它内部不一定是这样！
        正因为如此，以上观念除了官方的，我的那部分内容仅代表本人的理解。由此造成的后果，本人概不负责。

End