DAX 第七篇：迭代函數(shù)

敬鋒 2023-05-02 發(fā)布于廣東

展開全文

迭代是指逐行遍歷表，訪問一行叫做一次迭代，而每一次迭代得到的結(jié)果會作為下一次迭代的初始值。迭代函數(shù)至少擁有兩個參數(shù)：

第一個參數(shù)：要遍歷的行集
第二個參數(shù)：對同一行的不同字段要執(zhí)行計算的表達(dá)式。

一，迭代函數(shù)計算的過程

最常見的迭代函數(shù)是SUMX函數(shù)，第一個參數(shù)Sales是要迭代的表或表值表達(dá)式，第二個參數(shù)是逐行計算的表達(dá)式：

SUMX( Sales,
      Sales[Quantity]*Sales[Net Price]
)

迭代函數(shù)計算的過程：

Step1：要迭代的表或表值表達(dá)式受到篩選上下文的過濾，減少了數(shù)據(jù)表的總行數(shù)，也就是說，減少了迭代的次數(shù)，這個操作發(fā)生在迭代操作之前。

Step2：開始迭代，每一次迭代都會生成一個行上下文，在行上下文中計算表達(dá)式的值。

Step3：迭代函數(shù)把每一次迭代的結(jié)果聚合到一起，生成一個標(biāo)量值作為結(jié)果返回。對于SUMX函數(shù)，是把每一次迭代的結(jié)果進(jìn)行加和，最后返回總和。

二，迭代的基數(shù)

迭代的基數(shù)就是迭代操作執(zhí)行的次數(shù)，要提高迭代函數(shù)的性能，需要減少迭代的基數(shù)。

對于以下DAX表達(dá)式，迭代的基數(shù)是Sales表的行數(shù)，Sales表的行數(shù)越多，迭代的基數(shù)越大。

SUMX( Sales,
      Sales[Quantity]*Sales[Net Price]
)

對于嵌套的迭代函數(shù)，迭代的基數(shù)受到各個迭代函數(shù)的基數(shù)的影響，相當(dāng)于多層嵌套的循環(huán)，每一層循環(huán)都要逐行遍歷。

舉個例子，對于以下嵌套的迭代函數(shù)，其執(zhí)行的過程是：

SUMX(Product,
     SUMX( RELATEDTABLE(Sales),  
           Sales[Quantity]* Product[Net Price])
)

第一步：計算外層的迭代函數(shù)，對Product表進(jìn)行過濾，獲得被迭代的數(shù)據(jù)行集1。

第二步：開始迭代，對于Product的每一行，都生成一個行上下文，并把行上下文傳遞給內(nèi)層的迭代函數(shù)，

由于行上下文不執(zhí)行篩選，不能直接使用關(guān)系（原因一）
還由于內(nèi)層的SUMX迭代函數(shù)，也不能把外層行上下文轉(zhuǎn)換為篩選上下文（原因二），因此需要使用RELATEDTABLE函數(shù)，從Sales表中獲取相關(guān)聯(lián)的數(shù)據(jù)行集，作為內(nèi)層迭代函數(shù)要執(zhí)行迭代的數(shù)據(jù)行集2。
從Sales表中獲取到相關(guān)聯(lián)的數(shù)據(jù)行集2之后，開始執(zhí)行內(nèi)層SUMX的迭代計算，最終返回內(nèi)層SUMX迭代計算的結(jié)果。

第三步：迭代“第二步”，直到遍歷數(shù)據(jù)行集1的全部行，并把迭代的結(jié)果加和返回。

嵌套的迭代函數(shù)，代碼不夠簡潔，優(yōu)化之后的寫法避免了迭代函數(shù)的嵌套，在DAX引擎中執(zhí)行更為高效：

SUMX( Sales,
      Sales[Quantity]* RELATED(Product[Net Price])
)

三，在迭代函數(shù)中使用上下文轉(zhuǎn)換

如果在行上下文中使用了CALCULATE函數(shù)或度量，那么行上下文會被轉(zhuǎn)換為等價的篩選上下文。

詳細(xì)過程，請閱讀：DAX 第五篇：CALCULATE函數(shù)詳解

附：分位數(shù)函數(shù)

k表示期望的百分位值，其中INC是指inclusive（包含），EXC是指exclusive（不包含）。后綴帶EXC的函數(shù)，參數(shù)k的取值范圍是0-1，不包含0和1；后綴帶INC的函數(shù)，參數(shù)k的取值范圍是0-1，包含0和1。

PERCENTILE.EXC(<column>, <k>) 
PERCENTILE.INC(<column>, <k>)
PERCENTILEX.EXC(<table>, <expression>, k) 
PERCENTILEX.INC(<table>, <expression>, k)

當(dāng)指定百分位數(shù)的值介于數(shù)組中的兩個值之間時，這4個函數(shù)都會進(jìn)行插值。如果無法插入指定的k百分位數(shù)，則返回錯誤。

對于 INC函數(shù)，如果k不是1 /（n - 1）的倍數(shù)，則這4個函數(shù)將進(jìn)行插值以確定第k個百分位數(shù)的值。
對于 EXC函數(shù)，如果k不是1 /（n + 1）的倍數(shù)，則這4個函數(shù)將進(jìn)行插值以確定第k個百分位數(shù)的值。

PERCENTILE.INC

計算原理是：對于數(shù)組中的每個值，都會按照從小到大的順序給定一個百分位(基于n-1)，假如數(shù)組有n個數(shù)值，這n個百分位分別是：0/(n-1)、1/(n-1)、2/(n-1)……n-1/(n-1)，當(dāng)k值與這些百分位相同時，即k是1/(n-1)的倍數(shù)，直接返回數(shù)組中對應(yīng)的數(shù)值，如果k不是 1/(n-1) 的倍數(shù)，則 PERCENTILE.INC 使用插值法來確定第k個百分點的值。

PERCENTILE.EXC

計算原理是：對于數(shù)組中的每個值，都會按照從小到大的順序給定一個百分位(基于n+1)，假如數(shù)組有n個數(shù)值，這n個百分位分別是：1/(n+1)、2/(n+1)、3/(n+1)……n/(n+1)，當(dāng)k值與這些百分位相同時，即k是1/(n+1)的倍數(shù)，直接返回數(shù)組中對應(yīng)的數(shù)值，如果k不是 1/(n+1) 的倍數(shù)，則 PERCENTILE.EXC 使用插值法來確定第k個百分點的值。

引用簡書上《分位數(shù)計算，分析Excel中函數(shù)實現(xiàn)原理》的一個例子，作者是過橋0811 ：