本文是PowerBI星球嘉賓天行的投稿作品，使用DAX巧妙計算連續(xù)數(shù)據(jù)的問題，下面直接進入他的精彩分享。

-我是分割線-

這個DAX思路，幫你輕松解決連續(xù)數(shù)據(jù)計算

作者:天行

工作中經(jīng)常會遇到判斷連續(xù)的問題，比如連續(xù)打卡的天數(shù)、連續(xù)購買的顧客，或者連續(xù)正增長的銷售單位等等。

在普通的編程語言里面是很簡單的事情，一個循環(huán)加一個判斷就可以解決。

但在DAX里面，確實有點難度：

首先，DAX里面沒有循環(huán)語法；其次，DAX里面調(diào)試是個問題，特別是循環(huán)結(jié)構(gòu)的調(diào)試，對了，DAX里面叫做迭代器（iterator）。我們經(jīng)常會用到一個詞-”腦補“，這個過程真是誰用誰知道，非常需要六個核桃啊！

而判斷連續(xù)的問題需要至少兩重循環(huán)嵌套，還是有點挑戰(zhàn)性的。

好在，佐羅老師在《PowerBI DAX處理復(fù)雜業(yè)務(wù)到性能優(yōu)化1000倍》一文將問題抽象到處理【Index】和【Flag】兩列數(shù)據(jù)的程度，并提供了解決思路和實際算法，同時還將整個原理和優(yōu)化算法都解析得很清楚。

非常推薦大家去閱讀學(xué)習(xí)，至少我個人是獲益匪淺的，在這給佐羅老師說聲謝謝了！

問題是解決了，但我一直在想還有沒有優(yōu)化的可能，因為DAX的數(shù)據(jù)存儲原理是列存儲方式，即對列的聚合計算進行優(yōu)化。

也就是說，如果能把算法從普通的循環(huán)判斷變換成對某一列的匯總（sum）、計數(shù)（Count）或者最大、小值（Max、Min）上，才應(yīng)該是最優(yōu)化的DAX算法。

【尋找規(guī)律】

從上圖可以發(fā)現(xiàn)：

1、【Index】是步進值為1的整數(shù)

2、【Flag】有效位是1，無效位是空

稍做分析，可以得出結(jié)論：

【Index】列實際是全部按1的累加，如果將【Flag】列也累加，那么連續(xù)有效行的累加值也會呈現(xiàn)和【Index】一樣的規(guī)律。

在Excel里面很容易可以做個驗證：

增加兩列：

1、【累計Flag】

2、【累計Flag-Index】

相信一眼就可以發(fā)現(xiàn)規(guī)律：連續(xù)有效行的【累計Flag-Index】值是一樣的！

有了這個規(guī)律，對連續(xù)區(qū)間求最大值、最小值、計數(shù)，延伸至進一步對連續(xù)規(guī)律的分析就有辦法了。

下面就以最大連續(xù)天數(shù)為目標(biāo)，嘗試解決DAX編碼的問題。

【編碼思路】

1、首先寫出【累計Flag】的度量值

2、其次寫出【累計Flag-Index】的度量值

3、構(gòu)建一個計算表，對【累計Flag-Index】進行計數(shù)

4、取【計數(shù)】列最大值

這里，簡單說一句關(guān)于DAX調(diào)試的經(jīng)驗，就是將復(fù)雜問題拆分成一個個零件，生成零件后再進行封裝。

其中，借用計算表進行過程的調(diào)試，一定程度上可以減輕腦補的壓力。

【度量值封裝】

基本上將寫好的度量值進行組合封裝沒有什么難度，但在這個案例中的三個迭代器的用法還是值得一說。

1、SUMX

在【累計Flag】度量值中是用的Calculate+Filter的傳統(tǒng)組合

而在最終在ADDCOLUMNS的應(yīng)用中，用到的是行上下文，所以應(yīng)該使用SUMX迭代器函數(shù)來替代

相應(yīng)的寫法也有所不同，即通過IF函數(shù)對【Index】值進行判斷后，取小于等于當(dāng)前【Index】值的行進行迭代求和。

2、COUNTX

同上理由，生成聚合表時，不能使用常規(guī)的COUNT函數(shù)進行【累計Flag-Index】值的計數(shù)，只能使用COUNTX函數(shù)。

那么問題來了，使用常用的聚合函數(shù)SUMMARIZE和SUMMARIZECOLUMNS，居然不能正常進行計數(shù)！

通過構(gòu)建臨時計算表，發(fā)現(xiàn)所有的【累計Flag-Index】計數(shù)都是一樣的。

難道這是DAX的Bug嗎？

通過查找sqlbi的大神文章，

https://www./articles/nested-grouping-using-groupby-vs-summarize/

大致明白原理和解決方法：

基于SUMMARIZE和SUMMARIZECOLUMNS函數(shù)的設(shè)計原理，不能對SUMMARIZE動態(tài)生成的表再進行聚合計算。

解決方法就是使用GROUPBY函數(shù)，通過CURRENTGROUP關(guān)鍵字代表動態(tài)生成的表，作為COUNTX函數(shù)的第一個表參數(shù)。

3、MAXX

此處同樣應(yīng)該使用迭代器函數(shù)處理行上下文，而不能使用MAX函數(shù)。

至此，度量值封裝完成，看看最后的成果。

【性能測試】

從原理上分析，本算法只用到了一個累計求和、一個聚合計數(shù)和一個最大值，都是DAX非常擅長的基本功，而且隨著數(shù)據(jù)量的增加，計算量應(yīng)該只是線性增加，而不會出現(xiàn)指數(shù)級增長。

將佐羅老師的普通算法和優(yōu)化算法稍作修改成兩個度量值

利用Power BI5月份版本中新增的性能分析器進行測試

500條和1000條數(shù)據(jù)測試結(jié)果如下：

結(jié)果證實了推斷，新算法效率更高，而且數(shù)據(jù)量越大，優(yōu)勢越明顯。

【收獲小結(jié)】

1、加深了對DAX原理的理解：盡量利用DAX列存儲的特點，將問題抽象成對列的聚合操作，能大幅提升性能。

2、新掌握了一個聚合函數(shù)GROUPBY，針對聚合表中的迭代操作很管用。不過，通過查詢相關(guān)資料得知，三個聚合函數(shù)的性能比較是SUMMRIZECOLUMNS>SUMMRIZE>GROUPBY，基本上是1：2：3（消耗時間）的差別，切莫殺雞用牛刀。

3、進一步熟悉迭代器（X）函數(shù)的用法和用處：SUM的本質(zhì)是SUMX，這是使用聚合函數(shù)的核心概念。

開心！收工！

【后記】

文章寫完后，很唐突地請佐羅老師把把關(guān)。很快，他就給出了更優(yōu)化的算法，從DAX的角度來說，更優(yōu)雅更高效，經(jīng)過測試，可以再提升30%，真是山外有人，人外有人，學(xué)無止境?。?/span>

請大家欣賞大神的作品。

佐羅的代碼優(yōu)化了三個地方：

1、一次性得出【累計Flag】與【Index】的差額，減少了一個SUMX的損耗；

2、在SUMX中使用乘法取代IF函數(shù)，減少了公式引擎判斷的損耗；

3、用SUMMARIZE+COUNTROWS+FILTER的組合替代了GOUPBY+COUNTX+CURRENTGROUP的組合，大幅減少了損耗。

性能測試結(jié)果：

感謝佐羅老師的指點，知道自己又進步了的感覺真好！

結(jié)尾的話

感謝天行的分享，從尋找規(guī)律出發(fā)，逐步深入，幫我們在判斷連續(xù)性的問題上提供了解決方案。