PowerBI 職業專題-HR-在職與離職
- 2019 年 10 月 10 日
- 筆記
問題背景
HR 使用Excel已經可以統計很多內容了，但 BI 的特點，大家知道是 動態 的。本文講解如何在 PowerBI 中實現 HR 在離職人數的計算。
基礎數據
為了簡化問題，這裡僅僅使用兩張表。
離職人員
可以看到明顯的特點是對員工離職表，僅僅記錄必要的離職日期。
日期表
日期表。
問題重述
- 按年，月，部門計算當月離職人數；
- 按年，月，部門計算當月積累離職人數；
- 按年，月，部門計算當月在職人數；
值得注意的是，以上三個問題有一定的相關性。
數據模型
實現效果
設計與實現
在 PowerBI 中，編寫 DAX 來實現複雜計算邏輯時，常常會進入的坑包括：
- 有多種寫法，哪種寫法更容易理解；
- 動態性的保持與屏蔽；
- 度量值的起名。
分別來看度量值的實現：
KPI.人數.離職.當期 = COUNTROWS( VALUES( Data[工號] ) ) KPI.人數.所選.全部歷史 = CALCULATE( COUNTROWS( VALUES( Data[工號] ) ) , REMOVEFILTERS( 'Date' ) ) KPI.人數.離職.累計 = CALCULATE( [KPI.人數.離職.當期] , FILTER( ALL( 'Date'[日期] ) , 'Date'[日期] <= MAX( 'Date'[日期] ) ) , Data[離職時間] <> BLANK() ) KPI.人數.在職.當期 = [KPI.人數.所選.全部歷史] - [KPI.人數.離職.累計]
首先，注意命名：
這裡我們推薦使用 . 分隔符命名法。其約定在於：將主要的事情放在前面，將限定的部分放在後面，與 CALCUALTE 的寫法類似。
這是有原因的：
- 從排序上看，相關的度量值會排列在一起，便於使用；
- 從語義上看，與 CALCULATE 的語義達成一致，便於識別含義。
其次，注意動態性的保持與屏蔽。
所謂動態性，就是當用戶選擇切片時，可以仍然起到篩選作用。但是否應該起到篩選作用，設計師應該提前想好。在本例中，當用戶選擇不同部門或職能時，那麼所有的計算應該在該限定下完成，因此，我們必須保持這個動態性。
這就要求我們在使用 ALL 函數時，盡量作用到列，而不是一下將這個表都 ALL 掉。
如下：
度量值講解
KPI.人數.離職.當期 = COUNTROWS( VALUES( Data[工號] ) )
對人數的計算，應該以員工編號作為唯一標識，因此使用該列，同時，我們希望這個計算保持可被篩選的特性，因此使用 COUNTROWS( VALUES( T[C] ) ) 的固有定式。
KPI.人數.所選.全部歷史 = CALCULATE( COUNTROWS( VALUES( Data[工號] ) ) , REMOVEFILTERS( 'Date' ) )
這裡希望計算所有員工，因此要清除日期表 Date 的影響，在 2019.9 月，DAX 中引入了更加貼切的 REMOVEFILTERS 函數來實現這個業務語義。
KPI.人數.離職.累計 = CALCULATE( [KPI.人數.離職.當期] , FILTER( ALL( 'Date'[日期] ) , 'Date'[日期] <= MAX( 'Date'[日期] ) ) , Data[離職時間] <> BLANK() )
這是這裡最複雜的一個公式，其積累求和的定式為：
CALCULATE( [Measure] , FILTER( ALL( 'Date'[日期] ) , 'Date'[日期] <= MAX( 'Date'[日期] ) ) )
首先要理解這個定式，但在該案例中僅僅這樣是不行的，我們需要在離職的數據中考慮，因此多加了一個篩選條件。
我們在此前的文章中講解過上述定式，但幾乎 99.9 %的老鐵還是搞不明白，燒腦時刻來了。為了便於說明，我們重寫一遍：
CALCULATE( [Measure] , FILTER( ALL( 'Date'[日期] ) , 'Date'[日期] <= MAX( 'Date'[日期] ) ) )
以下為分析：
- 進入 CALCULATE 前，有一個篩選上下文，如 2019年3月。
- 進入 CALCULATE 後，最先運行到第三行，FILTER，進入之。
- 先執行 第 4 行 FILTER 的第一個參數 ALL( 『Date』[日期] ) ，得到全部日期序列。
- 執行 第 5 行，針對 第 4 行得到的全部日期序列，創建行上下文，並開始對這個序列進行迭代，對每一次迭代的數據行 『Date』[日期] 進行針對與 MAX( 『Date』[日期] ) 的比較的判斷。 這時複雜的問題來了，MAX( 『Date』[日期] ) 中的 『Date』[日期] 的含義是否與 第5行 第一次出現的 『Date』[日期] 含義一樣呢？ 這裡發生了非常複雜的情況，我們先來說明結果： MAX( 『Date』[日期] ) 的運算取決於進入 CALCULATE 前的篩選上下文。 會得到當月的最大日期。 因此，第 3 行 到 第 6 行可以得到截止於 進入 CALCULATE 前的篩選上下文中最大日期的日期序列。 （在 MAX 中發生了更複雜的情況，如果要理解這個問題，請參考另一篇文章，DAX 的 SUM 有多坑爹）
- 在 準備計算 CALCULATE 的第一個度量值參數時，我們來盤點一下當前的篩選環境
- 環境1： 進入 CALCULATE 前，有一個篩選上下文，如 2019年3月。
- 環境2： CALCULATE 的第一個篩選參數的執行結果 積累到2019年3月31日的所有日期序列。
- 在 環境1 和 環境2 的綜合影響下計算 [Measure]。 如果你仔細來思考，你會發現 環境1 和 環境2 的綜合影響應該得到 2019.03.01 到 2019.03.31，這並非預期效果。 因為這裡也發生了複雜的情況，解釋如下。
- 由於 『Date』 是日期表，DAX 引擎會自動為 CALCULATE 增加一個參數 REMOVEFILTERS( 『Date』 )，我們姑且稱之為環境3。
- 真正的執行順序是 環境3 清除了 環境1 的作用，而僅僅只有 環境2 在起作用，達到了預想的效果。
整個定式的實際如下：
CALCULATE( [Measure] , FILTER( ALL( 'Date'[日期] ) , 'Date'[日期] <= MAX( 'Date'[日期] ) ), REMOVEFILTERS( 'Date' ) // 由於'Date'[日期]被篩選，本行由DAX引擎自動添加 )
這裡需要大家反覆閱讀。
再談 MAX( 『Date』[日期] ) ，在上面的解釋中，為了連貫，沒有詳細解釋 MAX( 『Date』[日期] ) ，這裡為了徹底讓老鐵們明白這個問題，我們不妨打破砂鍋講到底：MAX( 『Date』[日期] ) 會被 DAX 引擎轉為 MAXX( 『Date』 , 『Date』[日期] )，我們進一步重寫一次這個定式，請注意用戶用手輸入的公式：
CALCULATE( [Measure] , FILTER( ALL( 'Date'[日期] ) , 'Date'[日期] <= MAX( 'Date'[日期] ) ) )
將被DAX引擎轉為：
CALCULATE( [Measure] , FILTER( ALL( 'Date'[日期] ) , 'Date'[日期] <= MAXX( 'Date' , 'Date'[日期] ) // 由 DAX引擎 轉換 ), REMOVEFILTERS( 'Date' ) // 由於'Date'[日期]被篩選，本行由DAX引擎自動添加 )
因此，我們總結如下：
- 第 3 行，FILTER 會創建針對 ALL( 『Date』[日期] ) 的迭代。
- 第 5 行，在 FILTER 的迭代里，MAXX 又會創建針對於 『Date』 的迭代。
- 第 5 行，MAXX 創建針對於 『Date』 的迭代所處的篩選上下文是 進入 CALCULATE 前的篩選上下文，如 2019年3月。
- 第 7 行，由 DAX 引擎添加。
綜上，我們實現了度量值的累計計算。
總結
本文值得反覆閱讀，直到您徹底清晰地理解這個重要的定式：
CALCULATE( [Measure] , FILTER( ALL( 'Date'[日期] ) , 'Date'[日期] <= MAX( 'Date'[日期] ) ) )
如果實在不能逾越，可以這樣理解：
VAR vDate = MAX( 'Date'[日期] ) RETURN CALCULATE( [Measure] , FILTER( ALL( 'Date'[日期] ) , 'Date'[日期] <= vDate ) )
這並不是為了說明 DAX 的複雜，在羅叔徹底理解類似這樣的定式以後，編寫DAX的感覺會變得更加自然，不必每次都真正思考篩選上下文在做什麼，一些定式會幫助我們走在正確的感覺上，只要略微調整就可以滿足我們的業務需求，如果實在需要刨根問底，可以像這樣一步步拆解研究，當然這個過程需要一定的耐心。看過本文，相信你的DAX能力又進階到了一個新的Level。
