PowerBI技巧之DAX CountX+RelatedTable实战：帮助HR妹子创建一个工作日历表.pdfVIP

之前介绍过用M语言创建自增长日历，这个日历属于单纯的自增长日历，适用于与对日期时间没有 殊差别的统 计报表使用。但是对于HR妹子，她们要统计加班记录，往往就需要知道是否有员工在周末或休息时间也去上班 了，所以，从而HR妹子的角度来看，一个标注有休息日和假期信息的日历才是好日历。要制作这种日历，也比较 简单。 先制作一个包含美国节假日的日历，这次创建基础日历表使用简单的DAX公式进行。 US Calendar = CALENDAR(DATE(2018,1,1),DATE(2018,12,31)) 之后可以提取出Month以及Day的相关信息。此处为了以后创建Report方便，对于Day的提取使用了FORMAT公 式，即按照Monday，Tuesday等更易读的形式提取日期，而不是直接提取数字。同时，使用了WEEKDAY函数， 将Monday，Tuesday等转换成了数字1-7，以便后续计算。 Month = MONT (US Calendar[Date]) Day = FORMAT(US Calendar[Date],DDDD) Weekday = WEEKDAY(US Calendar[Date],2) 基础数据准备完毕之后我们需要另外一组数据，即US的放假时间表。用万能的B ng查找一份，发 现http://www.calendarped /hol days/federal-hol days-2018.html 上记载的US Federal Hol days数据比较干 净。决定就用它了。 点击Power BI中的获取数据 （Get Date）- Web，输入包含US Federal Hol days的网址，然后选择确定 （OK）。 Power BI会自动加载网页中的数据，并将其“可能是”表单的数据提取出来。选择包含我们需要的hol day数据的表 单然后进行编辑。 Power BI自动分析出的表单中存在一点脏数据，根据我们的需求，可以将收尾两行去掉，并删除多余的column 3，然后整理出一份干净的hol day信息。 得到US Federal Hol days后我们需要跟之前的US Calendar表创建关联关系，这样才能将hol day 中的数据情况反 应在US Calendar表上。 搞定两张表之间的关系后就可以在US Calendar表中创建一个列来标记当前日期是否是工作日。公式如下： Isworkingday = IF(US Calendar[Weekday]0US Calendar[Weekday]6COUNTX(RELATEDTABLE(US Federal olidays If 中有三个判断，前两个比较好理解，即根据Weekday 中的值，把周一到周五这些日子标记成work ng day，后一 个调用了COUNTX+RELATEDTABLE函数来查看哪些日期是公共假期。至于是怎么查看的呢？先说一下 COUNTX的基本用法： COUNTX(table,expression) COUNTX的中英文解释都比较绕，它实际的意义是对一个表单数行数，怎么数呢？是根据express on定义的条件 进行数数。第二个参数express on部分多数填写的都是一个列名，也就是数数的对象，并且该对象只能是：数字， 时间和字符串。此外COUNTX是一个聚合函数，如果在计算列中使用，针对每一行的计算结果，实际上是整个表 单符合条件的结果。 COUNTX(RELATEDTABLE(US Federal olidays),1) 乍看一下COUNTX可实现的功能似乎与我们要根据US Federal Hol days表去标记US Calendar中Date列没什么关 系，但其实此处的核心其实RELATEDTABLE函数。这个函数可以从我们之前创建的US Calendar与US Federal Hol days两张表的1对1关系中通过筛选生成一整新的表，该表只包含US Calendar和US Federal Hol days两张表 中相同的数据。 RELATEDTABLE函数的用法非常简单： RELATEDTABLE(tableName) 由于此处我们新建了一个计算列，所以RELATEDTABLE函数的使用过程是：依次从US Calendar中拿出创建 relat onsh p使用的Date列下的每一行值，然后去relat onsh p另一端US Federal Hol days表中的date