前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >PowerBI 职业专题-HR-在职与离职

PowerBI 职业专题-HR-在职与离职

作者头像
BI佐罗
发布2019-09-29 15:52:08
2.8K0
发布2019-09-29 15:52:08
举报
文章被收录于专栏:PowerBI战友联盟
大家好,我是BI佐罗,罗叔。最近有特别特别多小伙伴问了很多和职场有实际关系的案例,我们会分专题来一一区分讲解。另外,对于 DAX 的很多特性直接去讲解,显得有些突兀。罗叔曾经阅读过一些技术书籍,为了讲解技术理念,会在一系列的实际案例中,逐步揭示。我们将按这个思路在实际案例中为大家带来解决问题以及感悟其中的公式。这里会涉及:设计模式,常见招式以及业务问题。

问题背景

HR 使用Excel已经可以统计很多内容了,但 BI 的特点,大家知道是 动态 的。本文讲解如何在 PowerBI 中实现 HR 在离职人数的计算。

基础数据

为了简化问题,这里仅仅使用两张表。

离职人员

可以看到明显的特点是对员工离职表,仅仅记录必要的离职日期。

日期表

日期表。

问题重述

  • 按年,月,部门计算当月离职人数;
  • 按年,月,部门计算当月积累离职人数;
  • 按年,月,部门计算当月在职人数;

值得注意的是,以上三个问题有一定的相关性。

数据模型

实现效果

设计与实现

在 PowerBI 中,编写 DAX 来实现复杂计算逻辑时,常常会进入的坑包括:

  • 有多种写法,哪种写法更容易理解;
  • 动态性的保持与屏蔽;
  • 度量值的起名。

分别来看度量值的实现:

代码语言:javascript
复制
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 掉。

如下:

度量值讲解

代码语言:javascript
复制
KPI.人数.离职.当期 = COUNTROWS( VALUES( Data[工号] ) )

对人数的计算,应该以员工编号作为唯一标识,因此使用该列,同时,我们希望这个计算保持可被筛选的特性,因此使用 COUNTROWS( VALUES( T[C] ) ) 的固有定式。

代码语言:javascript
复制
KPI.人数.所选.全部历史 =
CALCULATE( COUNTROWS( VALUES( Data[工号] ) ) , REMOVEFILTERS( 'Date' ) )

这里希望计算所有员工,因此要清除日期表 Date 的影响,在 2019.9 月,DAX 中引入了更加贴切的 REMOVEFILTERS 函数来实现这个业务语义。

代码语言:javascript
复制
KPI.人数.离职.累计 =
CALCULATE(
    [KPI.人数.离职.当期] ,
    FILTER( ALL( 'Date'[日期] ) , 'Date'[日期] <= MAX( 'Date'[日期] ) ) ,
    Data[离职时间] <> BLANK()
)

这是这里最复杂的一个公式,其积累求和的定式为:

代码语言:javascript
复制
CALCULATE(
    [Measure] ,
    FILTER( ALL( 'Date'[日期] ) , 'Date'[日期] <= MAX( 'Date'[日期] ) )
)

首先要理解这个定式,但在该案例中仅仅这样是不行的,我们需要在离职的数据中考虑,因此多加了一个筛选条件。

我们在此前的文章中讲解过上述定式,但几乎 99.9 %的老铁还是搞不明白,烧脑时刻来了。为了便于说明,我们重写一遍:

代码语言:javascript
复制
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 在起作用,达到了预想的效果。

整个定式的实际如下:

代码语言:javascript
复制
CALCULATE(
    [Measure] ,
    FILTER(
        ALL( 'Date'[日期] ) ,
            'Date'[日期] <= MAX( 'Date'[日期] )
    ),
    REMOVEFILTERS( 'Date' ) // 由于'Date'[日期]被筛选,本行由DAX引擎自动添加
)

这里需要大家反复阅读。

再谈 MAX( ‘Date’[日期] ) ,在上面的解释中,为了连贯,没有详细解释 MAX( ‘Date’[日期] ) ,这里为了彻底让老铁们明白这个问题,我们不妨打破砂锅讲到底:MAX( ‘Date’[日期] ) 会被 DAX 引擎转为 MAXX( ‘Date’ , ‘Date’[日期] ),我们进一步重写一次这个定式,请注意用户用手输入的公式:

代码语言:javascript
复制
CALCULATE(
    [Measure] ,
    FILTER(
        ALL( 'Date'[日期] ) ,
        'Date'[日期] <= MAX( 'Date'[日期] )
    )
)

将被DAX引擎转为:

代码语言:javascript
复制
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 引擎添加。

综上,我们实现了度量值的累计计算

总结

本文值得反复阅读,直到您彻底清晰地理解这个重要的定式:

代码语言:javascript
复制
CALCULATE(
    [Measure] ,
    FILTER(
        ALL( 'Date'[日期] ) ,
        'Date'[日期] <= MAX( 'Date'[日期] )
    )
)

如果实在不能逾越,可以这样理解:

代码语言:javascript
复制
VAR vDate = MAX( 'Date'[日期] )
RETURN
CALCULATE(
    [Measure] ,
    FILTER(
        ALL( 'Date'[日期] ) ,
        'Date'[日期] <= vDate
    )
)

这并不是为了说明 DAX 的复杂,在罗叔彻底理解类似这样的定式以后,编写DAX的感觉会变得更加自然,不必每次都真正思考筛选上下文在做什么,一些定式会帮助我们走在正确的感觉上,只要略微调整就可以满足我们的业务需求,如果实在需要刨根问底,可以像这样一步步拆解研究,当然这个过程需要一定的耐心。看过本文,相信你的DAX能力又进阶到了一个新的Level。

本文参与 腾讯云自媒体同步曝光计划,分享自微信公众号。
原始发表:2019-09-27,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 PowerBI战友联盟 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
目录
  • 问题背景
  • 基础数据
    • 离职人员
      • 日期表
      • 问题重述
      • 数据模型
      • 实现效果
      • 设计与实现
      • 度量值讲解
      • 总结
      相关产品与服务
      腾讯云 BI
      腾讯云 BI(Business Intelligence,BI)提供从数据源接入、数据建模到数据可视化分析全流程的BI能力,帮助经营者快速获取决策数据依据。系统采用敏捷自助式设计,使用者仅需通过简单拖拽即可完成原本复杂的报表开发过程,并支持报表的分享、推送等企业协作场景。
      领券
      问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档