一个简单现实案例挑战 PowerBI 水平测试 - 深度解析

近来，一个问题刷爆国内 PowerBI 圈子。

问题来自于真实业务场景，而且非常自然，如下：

某大型连锁企业（可能拥有1000个门店），运营层级分为：

- 大区

- 城市群/运营组

- 门店

每个门店由店长管理，店长的管理被评价得到KPI。现在希望按照如下结构显示，该如何实现？

当然，如果可以加入一个 TOPX % 滑杆更好，仅仅显示前 X% 的门店经理的绩效和排名。

初始实现

这个案例看上去非常简单，也很合理，其模型如下：

模型也是非常简单，如上所示。

几乎不需要任何进一步说明，其 KPI 是衡量一个店长在多个月的业绩综合表现，可以用平均值，如下：

KPI = AVERAGE( '绩效表'[绩效] )

于是就有了：

非常自然。

由于店和店长众多，业务小姐姐很快就有了一个非常合理的想法：

可以只显示前 20% 吗？并且把排名序号显示出来，就像 Exel 一样，向下一拖拽就好了。

小姐姐的这种需求太合理了，没理由不同意啊，以已经学习 DAX 1年的经验并且看了 2 遍《DAX圣经》的自信，应该可以在 1 小时搞定。

于是，欣然答应了小姐姐。

比你想得要难

然而，3天过去了…

只是想实现一个在 Excel 里如此容易的图：

小姐姐说：你到底行不行啊？

回答：行啊。

小姐姐说：那来啊~

可是可是~~

心里建设顿时崩溃了。

坐拥学习 DAX 1年的经验并且看了 2 遍《DAX圣经》的自信居然做不出这个。

发起挑战

罗叔和小伙伴讲，这个题目其实非常复杂，小伙伴不信，小伙伴准备了一切，如下：

就差一个度量值。

罗叔： 这个问题，看上去是非常简单的，其实有一定难度，很多人仅仅以为是考察 RANKX 的写法，其实，要超过这个范围。

本题价值

如果你不做，你看不出本题价值；如果你自己动手做了，那你就可以体会出很多东西。如果你做出来了，那您绝对是一流高手。

答题要求：非常简单：

不改变数据模型，按业务预期图，直接写度量值即可。

答案请严格对比：

不考虑 TOPX % 下也可以达到排名。

端午节没地方旅游，可以在家烧脑了，这个比做个图可有挑战性，据此前统计，99.999% 的 PowerBI 用户做不出来。不信？不信你下载了做做试试看。

欢迎大家下载这个问题，直接作答，并加入讨论群，如果做出来了，也可以提交答案。凡是参加该挑战的伙伴，最后都可以得到关于此题的深入解读，比你想象得更深更有价值。可谓一题洞悉 DAX 奥秘，CALCULATE 如何计算的？RANKX 如何计算的？在本题面前都逊色了。也希望大家可以提交来自真实环境的有价值题目，一起研究。

赶快行动吧。

解析与实现

问题分析，对于这个问题，从业务角度观察是一个非常简单的需求：

仅仅就是按照 KPI 增加一个 Rank 即可，这个增加如果在 Excel 中进行的话，其实就是拖一下即可。但在 PowerBI 中则需要用 DAX 在模型层进行计算而得到，这个问题变得有些复杂。

通过实践，会先后发现这个问题涉及的坑，这里一并指出：

• RANKX 的运行原理必须了如指掌。 （此事有难度，不是纯业务可以立马上手的）
• 运营大区存在按列排序列，如果清除某列的筛选，必须同时清除其按列排序列的筛选。 （此事很多人不知道）
• 人名有重复，导致这是一个略有问题的业务需求，需要使用 UID 来补充。

为了完整做对本案例，必须要同时深谙上述三点。

这里用相对技术一些的术语来描述这三点背后需要内化的认知：

• 掌握迭代，行上下文，筛选上下文，上下文转换，在筛选上下文中的行上下文。
• 某列若有按列排序，则该列和按列排序列是同时参与计算的，按列排序列处于隐藏状态。
• 业务使用名称列，但同时应该伴随主键列，确保名称唯一性。 （姓名是最容易出这个问题的）

上述内容，需要有 DAX 功底，这里不再赘述。

实现一：模型层计算

DAX 实现如下：

@ZM:RankV1 =
// Author：BI佐罗
IF( [KPI] <> BLANK() ,
    RANKX(
        CALCULATETABLE( VALUES( '绩效表'[UID] ) , ALLSELECTED( ) ),
        CALCULATE( [KPI] , ALL( '门店表' ) , ALL( '绩效表'[姓名] ) ),
        [KPI]
    )
)

在这里度量值里，其核心要义是：

对当前的元素，在 [UID] 列表计算 [KPI]，然后将当前 [KPI] 与之比对。

具体这里的 CALCULATE 和 CALCULATETABLE 需要仔细揣摩，这在我此前的 RANKX 详解文章中已经讲解过所有细节原理。那篇文章就是为了这里不再重复而准备的。

我们说这种算法叫模型层计算法，是因为在计算时回到了模型去进行计算。与之对应给出一个视图层计算法。

实现二：视图层计算

视图层计算（visual calculation），这件事是没有在 PowerBI 中真正实现的。SQLBI 有文章专门指出这个问题，可以参考：https://www.sqlbi.com/articles/a-proposal-for-visual-calculations-in-dax/

文中给出了这样的想法：

这里框出的两个函数，就是在 DAX 中并不存在的，而且其工作在可视化层。

在 Table AU 中，就原生有表计算以及快速表计算的特性，我们大概感受下：

在 Table AU 中，可以在其视图层构建计算。例如：

以及：

以及：

我们暂且不用纠结这里的原理，但很明显，这是符合“所见即所得”原则的。

而 PowerBI 中使用 DAX 构建公式却没有这样的便利，这是很多业务伙伴无法从 Excel 切换思维到 PowerBI 的重要原因。

而该问题已经在 PowerBI 社区被投票投成了热门：

视图级计算，是 PowerBI 的硬伤，在这个环境，PowerBI 暂时保持了精简的 DAX 运算系统，提供了在模型层面计算的能力，这种能力对于施加于模型的运算特别合适。而对刚刚入行的小白来说，在视图层计算利润率，占有率以及排名的时候就会好痛好痛。

现在给出本问题的近乎视图层的实现方式，如下：

@ZM:RankV2 =

VAR vCurrentValue  = [KPI]
VAR vConditionIsOK = NOT ISBLANK( vCurrentValue ) && ISINSCOPE( '绩效表'[姓名] )

RETURN IF( vConditionIsOK ,
    VAR tVisualTable =
        CALCULATETABLE(
            ADDCOLUMNS(
                SUMMARIZE(
                    '绩效表',
                    '门店表'[营运大区],
                    '门店表'[营运组&城市群],
                    '绩效表'[姓名],
                    '绩效表'[UID]
                ),
                "Value",
                [KPI]
            ),
            ALLSELECTED()
        )
    RETURN RANKX( tVisualTable , [Value] , vCurrentValue )
)

在本次的调查中，绝大多数（90%）参与的伙伴都是在这个方向上给出的作答。也进一步说明，大家还是更自然地在使用视图层计算的思路。当然，这也是很好的。一位伙伴（@他山之石）就很快在这个方向上给出了一个正确的答案（只是看不出是否考虑了重名问题）。我还记得在一年前，他很认真地和我交流 DAX 计算的问题，对很多 DAX 核心概念做深入思考，现在可以这么快做出这类计算，真的可能是基于有很扎实的 DAX 基础了。我来大致讲下这个这个视图级别计算的套路的核心。我将主体实现，精简下，大概如下：

@ZM:RankV2 =

VAR vCurrentValue = [KPI]

RETURN
    VAR tVisualTable =
        CALCULATETABLE(
            ADDCOLUMNS(
                SUMMARIZE(
                    '绩效表',
                    '门店表'[营运大区],
                    '门店表'[营运组&城市群],
                    '绩效表'[姓名],
                    '绩效表'[UID]
                ),
                "Value",
                [KPI]
            ),
            ALLSELECTED()
        )
    RETURN RANKX( tVisualTable , [Value] , vCurrentValue )

其核心思路，可以对照上面公式，详细描述如下：

1、这是视图层面的计算，因此，即使我们用肉眼看见的是“行”，而它其实是筛选环境，并没有“行”。我们称这样用肉眼看见的“行”而并非是数据模型表中的行，叫做“视图级行”。这种行实际会提供的还是筛选环境，也就是筛选上下文，而非行上下文。

2、设我们要定义的度量值叫做 M，M 在发生计算时，会受到上述 1 所说的筛选环境的影响。我们现在的思路是，在 M 的定义中克隆一个视图层筛选的现场环境来，我们称为：视图筛选环境克隆。这个视图筛选环境克隆的现场环境的表现形态，一定是一个表（table）。

3、在定义 M 的时候，要意识到的一个重要点在于：M 不仅仅受到 2 中所述的视图筛选环境克隆的影响，还同时继续遭受 1 中所述的原始筛选环境的影响。

4、视图筛选环境克隆如下：

CALCULATETABLE(
    ADDCOLUMNS(
        SUMMARIZE(
            '绩效表',
            '门店表'[营运大区],
            '门店表'[营运组&城市群],
            '绩效表'[姓名],
            '绩效表'[UID]
        ),
        "Value",
        [KPI]
    ),
    ALLSELECTED()
)

有了克隆环境，我们的计算就可以仅仅考虑克隆环境了。

5、静态化。静态化分为两手：1）当前的筛选环境下的视图行中的计算值；2）克隆环境的静态化。

6、用 VAR vCurrentValue = [KPI] 给出静态化。

7、用 VAR tVisualTable = … 给出静态化。

8、静态化计算。由 RANKX( tVisualTable , [Value] , vCurrentValue ) 给出。

可以看出在 8 中，是一个纯静态化计算，它可以确保在 RANKX 中的计算不再受到最原始筛选环境的影响。从而问题的解。

总结

本文内容，值得学习 PowerBI 和 DAX 的伙伴 N 次阅读，其中有些话语是为了你在第 N 次阅读时体会之用的。而对某些高手，已经可以意识到，我在这里给出了在目前的状态下，PowerBI DAX 如何实现视图层计算的通用套路。这个套路如此通用，以致于它可以一揽子解决几乎所有视图层计算的问题。我会将这些内容做一个专门的主题发出，并整理进入我的《PBI 高级》中。

本文的精华包括：

• 几个 DAX 的深坑；
• 来自业务本身的坑；
• 复杂模型层计算的探究；
• 视图层计算的探究。

这启发了我们做很多问题的方法。另外，它直接将我要提出的 PowerBI DAX 视图级通用计算模式呼之欲出。

你可以再看一遍：视图环境克隆 + 静态化 提出了使用 DAX 的新思路和玩法。香不香，好好感受吧。而震撼的 PowerBI DAX 视图级通用计算模式 就要来了。