20200803 PowerBI DAX 性能优化 高级视图算法

小伙伴催更了。准备放大招，所以，很多内容停滞了。如果本文的技巧让您震撼，那如果告诉您，本文仅仅是开胃菜呢。开始吹吧。

此前，有很多伙伴反映 PowerBI DAX 在进行某种运算时，速度随元素的增长会变得很慢，这个问题在很多重要的模型中都存在，包括了：

• 帕累托分析，当要计算的元素很多时；
• 累计百分比分析，当要计算的元素很多时；
• 其他模型。

本文先立足给出一种对比，后续文章再研究其他案例。

问题重述

已知用户列表，以及用户所产生的明细数据。显示：在动态筛选的界面中，给出用户列表以及对应的指标积累百分比。

先从效果看来理解这个问题，如下：

image

这非常容易理解，对于每个用户，用户Id表示该用户的唯一性；KPI表示该用户的某种指标；KPI%（≤Current）表示比当前元素（包括当前元素）的KPI值低的所有元素的KPI的积累百分比。

这非常像帕累托分析，在帕累托分析中，只不过是≥当前元素的积累百分比。

数据模型

用以下精简的数据模型来表示这个问题，有：

image

这一模型非常容易构建或模拟。

如果模拟这一数据模型，可以这样操作：

Item = 
SELECTCOLUMNS( GENERATESERIES( 1 , 10 ) , "Id" , [Value] )

其中，10表示元素个人，也可以替换为100，1000，10000，100000，1000000来逐步观察随着元素个数的增加，算法的用时成本。

对于明细数据，可以这样虚拟如下：

Detail = 
GENERATEALL( 'Item' , VAR X = RANDBETWEEN( 100 , 200 ) RETURN GENERATESERIES( X , X + RANDBETWEEN( 10 , 50 ) ) )

该模拟生成算法的意图为，对于每个元素，都从100到200之间随机给定一个数，并生成以该种子为起点的50个随机条目。

常规算法

熟悉 DAX 的伙伴或分析师很快就可以写出该问题的解法，如下：

Item.Percent%.ModelMethod = 
VAR vCurr = [Item.Value]
VAR tItemsAllSelected    = ALLSELECTED( 'Item'[Id] )
VAR tItemsFiltered       = FILTER( tItemsAllSelected , [Item.Value] <= vCurr )
RETURN COUNTROWS( tItemsFiltered ) / COUNTROWS( tItemsAllSelected )

我们称这一算法为模型算法，而其中的[Item.Value]度量值可以认为是通用指标计算的逻辑。

模型算法用时分析

对元素个数不断增加，可以发现在元素个数为8000的时候，算法需要消耗约15秒时间。（会因硬件配置有所不同）

这可以理解为：

如果有8000个用户以及明细数据，需要得到这样的积累占比分析，需要等待至少15秒钟，考虑到其他相关可视化图表的用时，这是无法接受的。

但这个算法，似乎已经是最好的了。在模型算法中，它使用了 VAR 暂存了数据，但似乎没有什么卵用啊。

于是，要如何进行优化呢？

答案是：在模型层面，是无法优化的。

该算法已经使用了相当正确的写法，并没有明显的问题，无法得到优化。

另辟蹊径：视图层算法

这里先给出结果，后面再做分析。

前面之所以叫模型算法，是针对这里要提出的视图（层）算法相对而言的。

什么是视图层算法？

如果计算不需要触碰底层数据模型，而仅仅需要在视图层面计算，我们说，这就叫视图算法。本例中，可以这样构造：

Item.Percent%.ViewMethod = 
VAR vCurr = [Item.Value]
VAR tView = CALCULATETABLE(
    ADDCOLUMNS( 
        VALUES( 'Item'[Id] ) , 
        "Value" , [Item.Value]
    )
    , ALLSELECTED( )
)
RETURN COUNTROWS( FILTER( tView , [Value] <= vCurr ) ) / COUNTROWS( tView )

关于视图层算法，我们已经在此前文章中给出过详细说明，这里不再赘述其原理。

值得一提的是，PowerBI 并不内置支持视图层计算，而由 SQLBI 发起的针对此特性的 PowerBI 社区投票得到非常多支持，但这个特性是否支持，以及如果支持后如何实现，对于微软的 PowerBI 团队，其实是一个难题。

但不管 PowerBI 是否原生支持，通过我们给出的几个案例，具有举一反三能力的伙伴应该已经发现自助实现视图层可视化计算的要领，这个要领几乎是呼之欲出的，我们将在后续文章给出开创性的实现思路以及通用做法。

在这里，我们称此处算法为视图层算法，我们检测其时间消耗成本，从实验看出，它比模型算法提升了性能，但并不显著。

视图层索引表算法

由于我们发现视图层算法相比模型层算法有加速的效用，我们只需要举一反三地构建所有可能的算法并进行比对就可以选择最优的模式。索引，显然是一个方向，这里直接给出其实现，如下：

Item.Percent%.IndexedViewMethod = 
VAR vCurr = [Item.Value]
VAR tView = CALCULATETABLE(
    ADDCOLUMNS( 
        VALUES( 'Item'[Id] ) , 
        "Value" , [Item.Value]
    )
    , ALLSELECTED( )
)
VAR tViewWithIndex = ADDCOLUMNS( tView , "Index1" , [Value] , "Index2" , [Id] )
VAR tIndexTable = DISTINCT( SELECTCOLUMNS( tViewWithIndex , "Index1" , [Index1] , "Index2" , [Index2] ) )
VAR tIndexedView = SUBSTITUTEWITHINDEX( tViewWithIndex , "Index" , tIndexTable , [Index1] , ASC , [Index2] , ASC )
RETURN ( MAXX( FILTER( tIndexedView , [Value] = vCurr ) , [Index] ) + 1 ) / COUNTROWS( tView )

可以看出，该算法是基于视图层算法改进而来的，对于 DAX 经验有限的伙伴，可能有理解的难度，但这并不是本文的重点，为了全面，我们把这一算法记录在案。通过对比，我们发现，该算法可以显著提升性能，如下：

image

随着元素数据的增加，IndexedView 算法可以有非常明显的性能改善，计算 10000 元素仅需 0.5 秒，比普通的模型算法提升了近 50 倍性能，这太神奇了。更值得惊讶的是，视图层索引表算法的编写比常规模型算法复杂得多，但却有超过 50 倍的性能提升，不可谓不凶残。

如果观察性能趋势图，普通的模型算法和视图层算法都近乎是指数级时间增加，这是我们不希望的。而视图层索引算法相对而言就平缓得多了。

视图层排序算法

是否还可以更进一步来加速这个算法呢？答案是肯定的。

我们巧妙地利用排序的性质，为每一个元素都进行排序，那么排序的序号正是它超过元素的个数。而这个排序仅仅需要在视图层完成计算，根本不需要触碰模型层，给出算法如下：

Item.Percent%.RankedViewMethod = 
VAR vCurr = [Item.Value]
VAR tView = CALCULATETABLE(
    ADDCOLUMNS( 
        VALUES( 'Item'[Id] ) , 
        "Value" , [Item.Value]
    )
    , ALLSELECTED( )
)
RETURN RANKX( tView , [Value] , vCurr , ASC , Skip ) / COUNTROWS( tView )

该算法非常简单，可以看出这也是基于视图层计算而进行的改进。我们来看看这个算法的时间消耗趋势，如下：

image

其效果是惊人的震撼，它面对100万元素的300万明细数据，仅仅需要不到3秒就可以计算完毕，在计算2万元素节点时，其性能是经典算法的上千倍。经过测试，对100万元素以及25亿明细数据，其计算用时约为3秒。

这可以从性能面板得到各种算法时间的对比，如下：

image

总结

本文抛开了传统的模型层算法，对于同一问题的解决，给出了视图层的等效算法，并将性能提升上千倍，这几乎是不可想象的。你如果问为什么会提升这么多性能，这里当然是触发了 DAX 最快计算的窍门，限于篇幅和复杂性，就不再展开，毕竟对于 99% 的伙伴，需要的永远是复杂和粘贴。如果你要问这是如何想到的，那必须归功于两点：其一，是对 DAX 本质的理解；其二，是发散思维。

然而，即使是提升了数千倍的性能，本文却还只是开胃菜，大餐正在烹饪中。

对于希望彻底理解 DAX 本质精髓的伙伴，罗叔准备了前所未有的 VIP 线下课程，彻底揭示 PowerBI 尤其是 DAX 的本质精髓。