PowerBI 职业专题-HR-在职与离职
问题背景
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。
腾讯云开发者
