Power BI DAX 求最大连续元素数的最佳实践
Power BI DAX 求最大连续元素数的最佳实践
在此前的文章中已经给出了 Power BI 使用 DAX 求最大连续元素数的方法。
【最大连续元素数问题】可以参考以前的文章，例如：
- 某人连续迟到的天数
- 某人销售业绩连续第一的月数
- 某企业销售连续同比超过去年的天数
- 某企业销售连续亏损的月数
- ...
抽象为数学问题后，就是：
1，0，1，1，0，0，0，1，1，1，0，1，1，0，0，1，1，...
的序列中，1 连续出现的最大次数。
数据结构
抽象为 DAX 问题以后，得到这样的 DAX 表结构，如下：
现在问题转化为：求 Flag 列连续出现最大的 1 的个数。
算法逻辑
求解该问题的一个非常实用的算法逻辑大致如下：
分成四步：
第一步，初始化，形成带序号的零一交错的表结构。这在 Power BI 的数据模型中是非常容易的。
第二步，取出特征值，将零点的位置全部取出。
第三步，错位取出下一个位置的索引。
第四步，求两个位置的差异并求整个序列的最大值。
Power BI DAX 经典解法
在没有 Power BI DAX 窗口函数前，我们给出的解法如下：
DDMethod = // 分治极限算法
VAR vT = SELECTCOLUMNS( FILTER( 'Data' , [Flag] = 0 ) , "SourceIndex" , [Item] )
VAR vX = ADDCOLUMNS( vT , "Index" , [SourceIndex] )
VAR vY = SUBSTITUTEWITHINDEX( vX , "Index" , SELECTCOLUMNS( vX , "Index" , [Index] ) , [Index] , ASC )
VAR vGroupCount = COUNTROWS( vY ) - 1
VAR vGroup = SELECTCOLUMNS( GENERATESERIES( 0 , vGroupCount ) , "GroupIndex" , [Value] )
VAR vGroup_WithBegin =
ADDCOLUMNS(
vGroup ,
"BeginIndex" , SELECTCOLUMNS( FILTER( vY , [Index] = [GroupIndex] ) , "value" , [SourceIndex] )
)
VAR vGroup_WithBegin_End =
ADDCOLUMNS(
vGroup_WithBegin ,
"EndIndex" ,
IF(
[GroupIndex] = vGroupCount , [BeginIndex] ,
SELECTCOLUMNS( FILTER( vGroup_WithBegin , [GroupIndex] = ( EARLIER( [GroupIndex] ) + 1 ) ) , "value" , [BeginIndex] - 1 ) ) )
VAR vGroup_Value = ADDCOLUMNS( vGroup_WithBegin_End , "Value" , [EndIndex] - [BeginIndex] )
RETURN MAXX( FILTER( vGroup_Value , [Value] > 0 ) , [Value] )其中，'Data' 是数据表。而整个过程就描述了这个算法，其难点恰恰在于对于一个序列如何给定顺序。
这里就作为练习供爱好者自己复制粘贴测试实践。在《BI 真经》的 Power BI 高级系列中给出了仔细的讲解，这里就不再重复了。
但很明显，这个解法并不是显而易见的，这里的确必须要使用 DAX 中复杂的几个函数来构建一些技巧的。
Power BI DAX 窗口函数解法
这就是一个必须使用 DAX 窗口函数的经典案例。
在上述问题使用 Power BI DAX 经典方法下，的确考察了综合使用 DAX 的高级能力，那里涉及到对 SUBSTITUTEWITHINDEX 函数的使用，以及组合复杂的 DAX 的技巧，我们仍然建议 DAX 高级玩家可以在脑中想出该问题的经典解法，但我们更希望 DAX 逐步演化成为一套更加简单的语言工具，便于业务人员直接使用。
现在，来看看使用 DAX 窗口函数可以如何优雅的解决这个问题，如下：
WindowMethod =
VAR vTable = CALCULATETABLE( SUMMARIZE( 'Data' , [Item] , [Flag] ) , 'Data'[Flag] = 0 )
RETURN
MAXX(
vTable ,
CALCULATE( SUM( 'Data'[Item] ) , OFFSET( 1 , vTable , ORDERBY( [Item] ) ) ) - [Item]
) - 1
解释如下：
第一步：
VAR vTable = CALCULATETABLE( SUMMARIZE( 'Data' , [Item] , [Flag] ) , 'Data'[Flag] = 0 )先获得一个 vTable，这里获得 vTable 的方法非常值得玩味。其逻辑上，仅仅是获得了带有零值的表。该表现在就是在算法逻辑中的第二步，如下：
现在的问题就是来求每个 index 所对应的下一个 index 以及他们的差值，如下：
MAXX(
vTable ,
CALCULATE( SUM( 'Data'[Item] ) , OFFSET( 1 , vTable , ORDERBY( [Item] ) ) ) - [Item]
) - 1注意 在本案例 DAX 中涉及的数据中，Item 其实是索引号，Flag 才是 0 值，不要混淆。
此时，对于 vTable 的任何一行，均通过 OFFSET 来找到下一行，而其差异值就是与当前迭代着的 vTable 的 [Item] 的差。
这样就迭代了所有元素并找到其中最大的。注意，真实的差异需要再减去 1。
方法对比
这两种方法几乎没有可比性了。
Power BI DAX 窗口函数在该问题中，以碾压性的优势赢过了经典方法。
其一，在编写的复杂程度上，DAX 窗口函数算法非常简单。
其二，其性能提升得更加明显，这让一个传统上复杂的问题，在瞬间就可以计算完毕。
其三，该 DAX 窗口函数方法没有什么副作用和缺点。
注意 这里其实用到了 DAX 窗口函数中存在的一个小小的特点，我们将在后续文章揭示出来。
总结
这里给出了三个有意义的事情：
1、这里给出了 Power BI DAX 窗口函数在真实解题中的一个必须的应用。它证明了 DAX 窗口函数不是鸡肋，而是必须存在的。
2、这里给出了最大连续元素数的经典解法，并超越了原来的非窗口函数解法。
3、这里让大家可以充分体会到 DAX 窗口函数在某些应用中的显著优势。