本文并非标题党，实事求是讲道理，而且让小白也能读懂，体会一份喜悦，还不快到PowerBI的碗里来，不信你就读完它。想象一下，如果从一开始到最后，你将一个案例的性能优化了10W倍，是怎样一种体验，这种体验终于出现在PowerBI中。首先感谢PowerBI战友联盟的战友【天行】，他重新提及了一个问题，随后开始一场优化的旅程，其实这背后还有很多的故事，本文就一起来看看这些好玩的事情。
业务背景
我们先来看几个很有意思的业务问题再说。
- 对于某员工，最近一个月，连续迟到的最大日数是多少？
- 对于某会员，最近12个月，连续每月购买的最大月数是多少？
- 对于某企业，最近10年中，每年都亏损的最大连续年数是多少？
- 对于某系统，最近一个月，连续异常的最大分钟数是多少？
- 对于某销售，最近6个月，连续业绩最后两名的最大周数是多少？
- 对于某APP，最近一年，各用户连续不登录的最大天数是多少？
- ...
请您自行补充更多的案例。这个业务背景可以给出两部分价值：
- 要求是连续的
- 找其中最大的
这其实蕴含着一个很重要的显而易见的常识，找到了最大的连续，就找到了问题的关键。
问题抽象
问题的抽象，是一个非常重要的技能。这项技能来自小学和初中的交界处。初中时还记得一直好奇一个问题，算术、数学和代数为啥名字变了，算术变成了代数。算术是 1 + 2，而代数是 X + Y，其本质就在于这帮助我们建立了抽象的思维习惯。代数的正确解释是：用字母代替数字，简称代数。（让你家小朋友问老师，很多初中数学老师居然回答不出这个问题~）
数字是具体的，字母是抽象而通用的，那么，我们把业务问题抽象后，去解决抽象一些的问题，如果问题可以得到解决，那就解决了一堆同质的问题。
例如：
image
通过观察，我们可以将如上所述的很多具体问题抽象为：
image
其中，Index表示序号，Flag表示标记，例如：1可以表示该序号处迟到，亏损，购买，异常等。
问题彻底转化为一个纯数字问题：一列由若干不确定位置的1中最大的连续个数。如果可以解决这个问题，那么上述的所有业务问题都可以轻松的计算出答案。当然，要求是在PowerBI中实现。
问题解决
该问题已于2018.11彻底解决，见此前发文：《PowerBI DAX处理复杂业务到性能优化1000倍》（看完本文再看）。最后得到一个结论：
image
该文给出了两种计算方法，快的方法的舒适区也仅仅在在1000个数以内，在计算10000个数时大致需要48秒。而慢的方法根本就无法胜任了。
在次日，《从数据到Excel自动化报表》作者，黄海剑老师给出一个建议：
image
黄老师大致的方法是：
image
这个方法的确是可以的，而且比原来的方法更加简单，速度也更快。在随后的工作中，我们也的确处理过类似的不少问题，但并没有将该方法补充进来。
直到PowerBI战友联盟的伙伴【天行】用类似的方法给出了一个实现：
image
作为一种完整，以及对事物追求完美的冲动，有必要给出一个终极的标准实现，于是就有了：
MaxContinueItems =
VAR vT1 = ADDCOLUMNS( SampleData , "累计" , VAR vX = [Item] RETURN SUMX( SampleData , ( [Item] <= vX ) * ( 1 - [Flag] ) ) )
VAR vT2 = SUMMARIZE( vT1 , [累计] , "出现次数" , VAR vX = [累计] RETURN COUNTROWS( FILTER( vT1 , [累计] = vX ) ) )
RETURN MAXX( vT2 , [出现次数] ) - 1
该方法非常简单（读者可自行Ctrl C+V使用），而且巧妙地处理了多个DAX可优化点，可以得到的性能是：
处理1000个数字，结果是：
image
不到0.2秒，非常好的结果。
这几乎是无懈可击的方法。
无巧不成书
就在同一天，一个用PowerBI展示系统监测异常的问题来了，问：最近1个月，主机系统的最大连续异常分钟数。哈哈，又是同样的问题，不过不同的是，它需要的数据点是 60分钟×24小时×30天= 43200，我们看看目前为止，最好的计算方法对此会是怎样，先来看看10000个数字的情况：
image
需要将近12秒的时间处理10000个数字。似乎，已经不能让人满意呢。接着模拟了大致60000个点的情况，等了1分钟，结果根本无法得到结果。等啊等啊等啊，终于出来结果了：
image
用时372秒，超过了10分钟。
提出问题的伙伴重金悬赏说可不可以优化到30秒以内给出结果，在重大的利益诱惑下，还是欣然接受了挑战，然后翻出了神书：
image
然而，并没有什么卵用。此处纯属娱乐，大家完全没有必要去看这些。但是，很多好的思想框架是需要的。
因为PowerBI根本不是C语言，也无法用通用的方式去实现，想着想着，就这样睡着了。在半梦半醒之间，突然梦见了一种莫名其妙的方法。然后早上起来，就这样输入到了PowerBI中，于是得到了这样的结果：
image
看得我惊呆了，太凶残了，3秒。
我又试了试，还是3秒，性能提升了100倍，不敢相信。
我又试了试，还是3秒，性能提升了100倍，不太敢相信。
我又试了试，还是3秒，性能提升了100倍，才信了。
到底梦到了什么呢，梦到了一个公式，如下：
image
不是无巧不成书，而成了天书，请原谅我也不知道这是什么意思了，想直接Ctrl C+V的如下：
MaxContinueItems.Premium =
VAR b = CALCULATETABLE( VALUES( Data[Item] ) , Data[Flag] = 0 ) VAR d = SUBSTITUTEWITHINDEX( ADDCOLUMNS( b , "xx" , [Item] ) , "iii" , b, [Item] , ASC ) VAR e = COUNTROWS( d ) VAR f = e / 53 VAR g = IF( f = INT( f ) , f - 1 , INT( f ) ) VAR h = ADDCOLUMNS( SELECTCOLUMNS( GENERATESERIES( 0 , g ) , "zz" , [Value] ) , "yy" , SELECTCOLUMNS( FILTER( d , [iii] = ( [zz] * 53 ) ) , "nb" , [xx] ) ) RETURN MAXX( ADDCOLUMNS( ADDCOLUMNS( h , "mm" , IF( [zz] = g , 1/0 , SELECTCOLUMNS( FILTER( h , [zz] = ( EARLIER( [zz] ) + 1 ) ) , "sb" , [yy] - 1 ) ) ) , "q" , VAR i = FILTER( Data , Data[Item] >= [yy] && Data[Item] <= [mm] ) VAR j = ADDCOLUMNS( i , "kk" , VAR c = [Item] RETURN SUMX( i , ( [Item] <= c ) * ( 1 - [Flag] ) ) ) VAR k = ADDCOLUMNS( SUMMARIZE( j , [kk] ) , "uu" , VAR c = [kk] RETURN COUNTROWS( FILTER( j , [kk] = c ) ) ) RETURN MAXX( k , [uu] ) - 1 ) , [q] )
不要问我为什么，你也不需要理解，总之，复制粘贴拿去用就是了。如果您真的特别好奇，请为您的好奇买单吧，成为PowerBI战友联盟的订阅会员，听佐罗老师视频讲解透彻背后的奥秘。没错，此处就是明目张胆的广告插入，你花的钱买懂这一个公式都是值得的。
善于观察的伙伴或问，里面为什么有个53？53是干嘛的？如果我回答因为一副扑克牌有53张，所以这里是53，你信吗？不管你信不信，确实如此。
完美的启示
令人吃惊的是，这个超级终极方法看上去无比复杂，却可以实现比堪称完美的方法提升100倍的性能，还有什么不可以呢？
我依稀清楚的记得，那些年在论坛里讨论奇葩的Excel公式的时光，而现在从Excel转型PowerBI的伙伴，你终于有更好玩的公式了，这个DAX公式，相信具有一定的里程碑意义，它启示了我们两点：
- 从复杂（原有公式）到简单（完美公式），再到复杂（超级完美公式），而本质却是更加简单（最高性能）。
- 没有最好，只有更好，优化是永无止境的，甚至是恐怖的。
从而，现实的业务问题得到了终极的解决。小姐姐的悬赏是30秒计算50000个点，而她得到了3秒计算60000个点的结果，超预期10倍，一天的心情都好了。
至此，我们得到了一个可以计算非常多类似业务模式的终极PowerBI方法，所有的人只要复制粘贴就可以了。
感谢过程中第一次提出业务场景的信克老师，黄老师，天行，神秘姐姐等，你们的鼓励，我们的动力。
总结
从给出的第一个方法到优化了1000倍的性能，发现仍然不行；进一步优化，得到了近乎完美的化简；由于应对更复杂情况，仍然不行；进一步优化了100倍的性能，得到了总计10万倍的性能优化，毫不夸张。
PowerBI战友联盟，定位于PowerBI世界最前沿，本文就是注解。将一堆现实问题找出共同模式，然后归纳出无懈可击的完美解决方案就是我们要做的。我们结合众多实力战友，用打造奢侈品的态度调教和打磨每个细节。
如果你非常愿意学习PowerBI，又不差钱，可以学习我们的VIP课程，就像公式一样，可以急速减少优化您成为高手的时间，也许比普通人快100倍哦。尊敬的会员同志们，视频已经在你们的文件夹，下载后观看BI佐罗为您讲解天书级完美公式。
网友评论