PowerBI DAX處理複雜業務到性能優化1000倍

• 2019 年 10 月 6 日
• 筆記

本文考察對DAX的真正掌握程度。

本文將帶你完成一次 PowerBI DAX 的神奇之旅，如果您是 DAX 的熟練選手，可以試試以下題目。

本文將從很有業務價值的問題出發，抽象出模式進而設計算法並用DAX（在空中：幾乎無法測試，必須非常熟練）完成計算；隨後發現性能不足的問題，然後通過仔細觀察和優化，將性能提升恐怖的 1000 倍。

DAX 是 PowerBI 中的函數語言，並非通用類編程語言，對於很多問題，無法像編程語言一樣設計解決思路，需要另闢蹊徑。而使用 DAX 設計的算法是否可以達到性能最優也是一個問題。 本文達成兩個預期：

• 編寫一個解決複雜業務問題的DAX算法
• 對該算法進行性能優化 並展示一個好玩的現象：
• 普通算法與優化算法的性能對比
• 10000行邏輯查詢的性能是可能由於1000行查詢邏輯的

這裡的每個問題都十分驚艷，讓我們一起來了解。

問題重述

在很多情況下，我們會遇到以下場景：

• 對於某員工，最近一個月，連續遲到的最大日數是多少？
• 對於某會員，最近12個月，連續每月購買的最大月數是多少？
• 對於某企業，最近10年中，每年發展都增長的最大連續年數是多少？

大家可以自行考慮或嘗試實現以上問題的 PowerBI 中 DAX 實現。這並不是一個簡單的問題。

問題抽象

為了更好地理解本問題，並為未來擴展留有機會，這裡對上述問題進行抽象，如下：

可以看出對於上述問題，均可以描述成由核心兩列完成計算的過程。因此，可以對該問題做進一步優化，得到：

對問題進行進一步加工抽象，可以得到：

• Index 列，與行號類似。
• Flag 列，指明該用戶或產品在當期有效（真實環境中）。

於是問題轉化成了從Index與Flag構成的表中尋找答案。

DAX 算法設計

本案例中描述的問題比較複雜，由於DAX中是沒有循環結構，導致無法使用循環結構來處理問題。歡迎 DAX 高手提供你想到的好方法。

在 PowerBI DAX 中，我們可以通過技巧來實現類似循環結構的效果，我們將這個效果用於本案例，首先來看下算法示意圖：

大家可以思考本問題的本質是幾層循環結構？

按照上圖的算法思路，我們考慮如下：

• 對於[Index]的每一行
• 建立從起始位置到當前[Index]位置 n 的結構
• 對於該結構的每行 m
• 建立從 m 到 n 的結構
• 如果 m 到 n 全是 1 ，則該行為連續滿足行
• 獲取連續滿足行的最大值，則得到連續滿足條件的最大值
• 再獲取連續滿足條件的最大值的最大值

因此，可以發現對於這裡的業務問題涉及3層循環結構，在DAX中很可惜是不支持循環結構的。

在本文為訂閱會員錄製的視頻中將詳細描述該算法的內容以及DAX實現。

DAX 算法實現

這裡使用技巧來實現需求，直接上 DAX 算法如下：

如果沒有算法設計，光靠肉眼閱讀，很難理解該DAX表達式，何況把它寫出來了。如果您有更好的實現方式，歡迎留言交流。

Source 的示意結構以及計算完成的結構為：

通過對 Source 表加入一個 Value 列來計算每行的結果。

DAX 性能評估及優化

如果將下圖的面積部分視作 DAX工作的負荷，則：

可以看出，凡是出現 1 的位置，都會做一個從頭到當前位置的迭代，因此總的算法規模大致在： n ( 1 + n ) n / 2 ，大致為 n 的三次方規模，其中 n 為行數。

通過增加行數來看看算法的可用性隨着時間的變化：

也就是說，當迭代行數達到1000行時，所需時間規模在6分鐘（原單位為毫秒，1秒=1000毫秒）。這是一個不可接受的性能。當然在實際的操作中，可能並不需要有大到1000規模的迭代。

算法的優化設計

對於上述的算法，其實已經做了少許優化，算法並不考察每一行，而是僅僅考察Flag=1的行，這樣已經減小了計算規模，但遠遠不夠。其實還可以在優化，我們仔細再研究該問題後，可以得到這樣的算法思路：

對比之前的算法，從觀察面積表示了算法的計算規模（消耗時間）可以看出優化的算法，可以大幅提升性能。其思路是：不從開始位置迭代，不然會產生大量無效迭代計算，優化的算法從1的位置開始迭代，因此可以大幅度縮減計算規模。

如果再進一步仔細觀察，會發現如果數據中存在大量的獨立點1，也就是說：幾乎都是偶爾遲到1次，很少出現連續多次遲到，這是一種稀疏情形，那麼還可以做更進一步的優化，將針對第一個 1 的迭代全部去除，以降低大量稀疏的 1 帶來的運算量，這種運算也是意義不大的，算法進一步改進如下：

可以再次通過面積來直觀對比，可以發現所需面積大幅度下降，也就是性能再次大幅提升。

如果原問題是帶有大量的稀疏的 1 的，全部排出後的算法複雜度大致為：

k ( 1 + k ) k / 2 ，其中 k << n ，n 為行數，k 為最終的答案值， 且遠遠小於 n。

DAX 改進算法的實現

我們看看它的DAX表達式：

高亮圈選的內容就是優化的核心所在。會員視頻提供詳細講解，實在不好用文字表達。強烈推薦研究本算法，是提升DAX水平的絕佳案例。

用 DAX Studio 觀測性能優化效果

首先來比較一下優化前後，DAX引擎對DAX表達式的處理，也就是翻譯成DAX引擎可以執行的邏輯，改良前的邏輯查詢達1000行；而改良後的邏輯查詢達10000行；問題來了：1000行的效率會比10000行更高嗎？截圖如下：

優化前：

優化後：

我們分別記錄不同量級下的查詢耗時來進行分析。

性能實際測試分析

如下所示：

這是在 100 行數據以內，兩種算法效果的對比。這反應了在 60個元素以內，優化算法反而看不出優化。

隨着數據量的增長，優化算法的優化被慢慢顯現出來，如下所示：

這兩條曲線太有意思了。可以看出隨着時間的變化，優化算法可以保持很好的穩定性，但普通算法在 60 個元素以後就會大幅來到性能瓶頸。優化算法可以處理5000元素在10秒以內完成。也就是說500個用戶在過去12個月的最大連續購買月數。我們在DAX中運行可以看到非常明顯的差異。

其中，在 超過1200個 元素時，普通算法耗時：

優化算法耗時：

性能差距超過1000 倍。如此大的性能提升，完全依靠算法設計的改進，這在實際業務中也是不多見的，並非所有的解決方案都有很大的改進空間。

為何優化後的查詢更複雜，而效率反而更高

大家可以留意到優化後的查詢多達10000行；而優化前的查詢大致是1000行。 由於查詢複雜度增加了10倍，因此，表現出：

• 60以內的元素，普通算法勝出；
• 100以上元素，優化算法大幅勝出。

這也是本案例的一個很有意思的地方。

總結

本文通過實際案例講述了：

• 複雜DAX的算法設計流程：形象的圖示法。
• 算法優化流程：避免不必要的計算開銷。
• 算法性能的優化：在一定數據量級下可以達到1000倍的差距。

因此，本文內容在有着巨大的實際業務價值的同時還有着巨大的示範意義。雖然本文給出了算法示意與DAX表達式，但強烈建議讀者自行思考並實踐本案例，本案例從複雜度及適用性來講都是DAX中不可多得的好案例。

——

本文含佐羅視頻講解，年度訂閱會員自動更新。決定成為PowerBI 高手的夥伴幾乎都是 Excel120 訂閱會員。本文就是最好的例證。

成為高手，不再猶豫