Power BI 用DAX解決數據的循環迭代
一、循環迭代的難處
實際業務中，數據迭代應用廣泛，如在複利現值、折舊等方面的計算，然而眾所周知的是，DAX是一個基於列引擎的函數語言，數據中每行基於其各自行上下文計算，但我們需要的效果是，列的第二行的數據基於第一行數值的計算產生，以此類推，列的第n行數據基於其第n-1行求得，這樣的操作在Excel可以很輕鬆求出(如下圖中的A、B、C三列)：
但在Power BI（或PowerPivot）中，你雖然可以基於某個列利用諸如SUMX等函數進行迭代運算(如下圖所示的兩種方法)，
但卻難以基於某列的第一行的值，迭代該列自身而求出該列，換句話說，你可以很輕鬆依據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的值，該公式都返回了正確的循環迭代結果。
【註：本文首發於PowerBI星球公眾號，案例源文件已分享至PowerBI星球-知識星球】
推薦閱讀：
