當Power BI遇上數學：用DAX解決數據的循環迭代

本文來自PowerBI星球嘉賓Davis的分享，Davis曾在知乎上發表過數篇高質量的PowerBI文章，是不折不扣的PowerBI技術大神。當我邀請他為星友們也分享一些乾貨時，他欣然同意，於是有了這篇精彩的文章，下面就請大家慢慢欣賞吧。

用DAX解決數據的循環迭代

Davis(知乎@九州殊口增七)

一、循環迭代的難處

實際業務中，數據迭代應用廣泛，如在複利現值、折舊等方面的計算，然而眾所周知的是，DAX是一個基於列引擎的函數語言，數據中每行基於其各自行上下文計算。

但我們需要的效果是，列的第二行的數據基於第一行數值的計算產生，以此類推，列的第n行數據基於其第n-1行求得，這樣的操作在Excel可以很輕鬆求得(如下圖中的A、B、C三列)，

但在Power BI（或PowerPivot）中，你雖然可以基於某個列利用諸如SUMX等函數進行迭代運算(如圖2所示的兩種方法)，

但卻難以基於某列的第一行的值，迭代該列自身而求出該列，換句話說，你可以很輕鬆依據A列的篩選上下文利用SUMX等類似的函數在B列求出其迭代結果（或在自定義的度量值得出結果）。

但卻難以實現當某列只有第一行有數值時，如何通過迭代其自身得出其第2至第n行的值，這樣的計算在Excel很輕鬆，但用DAX卻不易實現。

二、迭代的本質是數列

事實上，DAX同樣可以完美解決該問題，因為正如本段標題所言：迭代的本質是數列。當你用數列的思維去思考在Power BI的迭代問題，就能簡單多了。

因此，針對於上圖A列的情況：當列的n 1行等於第n行加d時，整列的數據其實就是一個公差為2的等差數列，如下，我們只需要利用等差數列公式即可：

同理，如果情況如同B列：n 1 = n * 2, 那麼這就是一個等比數列：

三、如何解決加減乘除類型的混合迭代

正如圖1的C列。當迭代公式類如「f(n 1) = (f(n) d)*q」時，其邏輯就不像上面公式簡單了，但實際上，這是一個數學問題了，本文在此不做拓展。

其答案是：儘管此時數列f(n)本身是不規則數列，但f(n) – f(n-1)卻是一個規則數列，而且是等比數列。

依據這個思路，我們的可以先利用等比數列規則求出f(n) – f(n-1)，然後反推出f(n)，實際上就是高中數學的差分法了。首先，f(n) – f(n-1)的計算公式為：

注1：以如圖1的C列為例，其公差為3，公比為2，則f(n) – f(n-1)為首項為f(2)-f(1),公比為2的等比數列；

注2：第14、15行處，由於差分法會使第一項留空，故使用IF(ISBLANK(),,)使第一行有值。

在得出f(n) – f(n-1)後，即可反推出f(n)的值，因為f(n)是一個以f(n) – f(n-1)為公差的不等差數列，計算如下：

這樣我們就成功完成了在這種複雜公式計算下的循環迭代。經測試，無論我們在公式中如何改變D和Q的值，該公式都返回了正確的循環迭代結果。

感謝Davis的精彩分享，大家以後再碰到數據迭代相關的難題，都可以借鑒本文的思路。星友們遇到問題時，也可以在知識星球中隨時向Davis提問哦。

喜歡這篇文章嗎？立刻分享出去讓更多人知道吧！