DAX CountX+RelatedTable实战：帮助HR妹子创建一个工作日历表

之前介绍过用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 = MONTH('US Calendar'[Date])
Day = FORMAT('US Calendar'[Date],"DDDD")
Weekday = WEEKDAY('US Calendar'[Date],2)

基础数据准备完毕之后我们需要另外一组数据，即US的放假时间表。用万能的Bing查找一份，发现http://www.calendarpedia.com/holidays/federal-holidays-2018.html 上记载的US Federal Holidays数据比较干净。决定就用它了。

点击Power BI中的获取数据（Get Date）- Web，输入包含US Federal Holidays的网址，然后选择确定（OK）。

Power BI会自动加载网页中的数据，并将其“可能是”表单的数据提取出来。选择包含我们需要的holiday数据的表单然后进行编辑。

Power BI自动分析出的表单中存在一点脏数据，根据我们的需求，可以将收尾两行去掉，并删除多余的column 3，然后整理出一份干净的holiday信息。

得到US Federal Holidays后我们需要跟之前的US Calendar表创建关联关系，这样才能将holiday中的数据情况反应在US Calendar表上。

搞定两张表之间的关系后就可以在US Calendar表中创建一个列来标记当前日期是否是工作日。公式如下：

Isworkingday = IF('US Calendar'[Weekday]>0&&'US Calendar'[Weekday]<6&&COUNTX(RELATEDTABLE('US Federal Holidays'),'US Calendar'[Date])<1,"Yes","No")

If中有三个判断，前两个比较好理解，即根据Weekday中的值，把周一到周五这些日子标记成working day，后一个调用了COUNTX+RELATEDTABLE函数来查看哪些日期是公共假期。至于是怎么查看的呢？先说一下COUNTX的基本用法：

COUNTX(<table>,<expression>)  

COUNTX的中英文解释都比较绕，它实际的意义是对一个表单数行数，怎么数呢？是根据expression定义的条件进行数数。第二个参数expression部分多数填写的都是一个列名，也就是数数的对象，并且该对象只能是：数字，时间和字符串。此外COUNTX是一个聚合函数，如果在计算列中使用，针对每一行的计算结果，实际上是整个表单符合条件的结果。

COUNTX(RELATEDTABLE('US Federal Holidays'),1)

乍看一下COUNTX可实现的功能似乎与我们要根据US Federal Holidays表去标记US Calendar中Date列没什么关系，但其实此处的核心其实RELATEDTABLE函数。这个函数可以从我们之前创建的US Calendar与US Federal Holidays两张表的1对1关系中通过筛选生成一整新的表，该表只包含US Calendar和US Federal Holidays两张表中相同的数据。
RELATEDTABLE函数的用法非常简单：

RELATEDTABLE(<tableName>)

由于此处我们新建了一个计算列，所以RELATEDTABLE函数的使用过程是：依次从US Calendar中拿出创建relationship使用的Date列下的每一行值，然后去relationship另一端US Federal Holidays表中的date列下查看是否有匹配的数据，如果有，就会用这个date生成一个新表。之后外围的COUNTX顺势就会计算当前刚刚生成的表有几行，然后返回行数。（COUNTX的expression填写了一个常量1，目的就是不以任何条件的数当前表的行数。）。当US Calendar中Date列下的某行值在US Federal Holidays中不存在时，就会返回BLANK。而当COUNTX计算的表是BLANK时，它的返回结果也会是BLANK。

通过这种方式，我们实际上是在US Calendar中创建出来一个列，如果当前date是Federal Holiday，当前列的结果就是1（US Calendar表中没有重复日期，所以只有唯一匹配，因此COUNTX的结果肯定是1。）；如果不是，列值就是BLANK。

在得到了哪些日期是公共假期之后，我们就可以顺利的标记出working day了。

美国的日历制作完毕，同理还可以制作一个中国区的日历。不过与美国的放假规则不太一样，国内的假期有串休的概念，即某些周六周日是正常的工作日。因此针对这种情况，中国区的日历要比美国的稍微复杂一些。我们需要将国内的放假情况做一个拆分，只做两张表，一张是单纯记录周一到周五属于法定节假日的情况，另外一张则是记录周六和周日是法定上班时间的情况。

搞定两张表之后可以与CN Calendar创建关联关系然后使用相同原理来标记工作日。

Isworkingday = IF(('CN Calendar'[Weekday]>0&&'CN Calendar'[Weekday]<6&&COUNTX(RELATEDTABLE('CN Holiday'),1)<1)||COUNTX(RELATEDTABLE('CN Weekend Work'),1)=1,"Yes","No")