32、应用日期表时的注意事项

在PowerBI中，日期维度是非常常见的一个维度，基本上各种数据分析需求都会对日期范围有所要求，也就是说各个事实表基本上都会存在日期字段，所以在PowerBI中的一个最佳实践就是创建日期表来统一管理日期维度，这就要求我们对日期表的一些特殊行为与细节有一定的了解，这也是本篇文章所要讲述的内容。

日期表的创建

由于日期表的重要性，因此在创建日期表时一般需要满足一定的要求，具体如下：

• 日期范围需要能覆盖整个模型中的所有数据日期，可根据业务情况前后扩展几年
• 日期范围应该包含涉及到的年份的完整日期，即以年为单位
• 日期应该连续，没有缺失，而且每个日期都应该是唯一的，不能有重复
• 可按分析需求添加年份、季度、月份、财年、节假日等各种附加属性

创建日期表的具体方法并没有限制，毕竟日期表也仅仅只是一张表而已，可以在其他工具中创建好后再导入到PowerBI中，只要能够满足上面的要求即可。下面给出使用DAX创建日期表的代码：

日期表 = 
var startdate=DATE(YEAR(MIN('事实表'[日期])),1,1)
var enddate=DATE(YEAR(MAX('事实表'[日期])),12,31)
RETURN
ADDCOLUMNS(
    CALENDAR(startdate,enddate),
    "Year Number",YEAR([Date]),
    "Year",FORMAT([Date],"yyyy"),
    "Month Number",MONTH([Date]),
    "Month",FORMAT([Date],"mmmm"),
    "Quarter Number",ROUNDUP(MONTH([Date])/3,0),
    "Quarter","Q"&ROUNDUP(MONTH([Date])/3,0),
    "WeekNum",WEEKNUM([Date],2),
    "WeekDay Number",WEEKDAY([Date],2),
    "WeekDay",SWITCH(WEEKDAY([Date],2),1,"周一",2,"周二",3,"周三",4,"周四" ,5,"周五",6,"周六" ,7,"周日"), 
    "YearMonth Number",YEAR([Date])*100+MONTH([Date]),
    "YearMonth",FORMAT([Date],"yy-mmmm"),
    "YearQuarter Number",YEAR([Date])*10+ROUNDUP(MONTH([Date])/3,0),
    "YearQuarter",YEAR([Date])&"Q"&ROUNDUP(MONTH([Date])/3,0)
)

将上面的代码粘贴到新建表的公式编辑栏即可，以上代码仅供参考，可根据实际分析需求自定义修改或添加各种附加属性。

日期主键的指定或创建

在理解日期主键的特殊行为之前，先要理解什么是日期主键，以及日期主键怎么创建等。所谓的日期主键可以通过以下方式进行指定：

1、在“标记日期表”选项中指定的日期字段。

2、日期表与其它表建立关系时，若日期表为一端，则日期表中用于连接关系的日期字段会自动成为日期主键。若日期表中用于连接关系的字段不是日期类型的，那么并不会使其成为日期主键，该方式仅限日期类型的关系连接字段。

3、在模型视图的属性面板里的“主键”选项中指定的日期字段。

以上三种方式均可以指定日期主键，其中需要注意的可能是第二种方式，因为第二种方式并不需要我们手工设置什么东西，仅仅只是把日期表与其它表建立了关系就会使一端的日期表的关系连接日期字段自动成为日期主键。由于连接关系是很正常也很普通的一件事，所以如果不是事先就清楚这个特性，那么这种“无感“体验有时候反而容易带来意想不到的结果或错误。

日期主键的特殊行为

日期主键有一个特殊行为，即：日期主键字段上的筛选器可以覆盖日期主键所处的基础表的扩展表上的所有字段的筛选器。其中定语可能有点多，如果没看明白可以多读几遍。简单来说就是日期主键字段上的筛选器可以覆盖某个表上的所有筛选器，而这个表通常是日期表的扩展表。

为了帮助理解，下面先来看一个行上下文转换的例子（此时未指定日期主键）：

在该例子中，有一个计算列，且使用了CALCULATE函数，因此会使计算列中的行上下文转换成筛选上下文，从而得到日期、产品、销量三个筛选器，并且这三个筛选器筛选的内容都等于当前行中各自对应的值。比如在第三行中，行上下文转换后得到的三个筛选器为：

'表'[日期]=dt"2022/1/3"
'表'[产品]="A"
'表'[销量]="10"

然后计算列的CALCULATE函数中定义了一个内部筛选器，该内部筛选器为日期列上的筛选器，因此会覆盖行上下文转换后得到的日期筛选器，覆盖后的计值环境中存在的筛选器如下：

'表'[日期]=dt"2022/1/1"    //因内部筛选器覆盖，筛选的日期变为2022/1/1
'表'[产品]="A"
'表'[销量]="10"

最后CALCULATE函数的第一参数在上面这个计值环境中计算，而上面这个计值环境刚好能够筛选出第一行的数据，因此第三行的计算列的结果返回10。由于这个表的数据都是特殊设计过的，所以同理，前三行的结果都会返回10。但是从第四行开始，行上下文转换后的日期筛选器被CALCULATE内部筛选器覆盖成2022/1/1后，再与其它的产品与销量筛选器相交时，并不能过滤出任何数据，所以从第四行开始计算列的结果都为空。

上面这个例子只是一个普通的行上下文转换的案例，由于CALCULATE内部的日期筛选器只能覆盖行上下文转换后的日期筛选器，而无法消除产品与销量的筛选器的影响，导致部分过滤不到数据的行的结果为空。

那么下面引入日期主键，通过”标记日期表“选项，将日期字段设置为日期主键，如下图所示：

然后再来看下该计算列的结果，如下图所示：

可以发现，在设置了日期主键后，在计算列的表达式保持一致的情况下，其结果竟然整列都返回10了。

其中的原理就是本节开头所提到的日期主键的特殊行为：日期主键字段上的筛选器可以覆盖日期主键所处的基础表的扩展表上的所有字段的筛选器。所以在设置了日期主键后，CALCULATE的内部日期筛选器可以覆盖行上下文转换后得到的所有筛选器，使得CALCULATE第一参数的最终计值环境只剩下一个日期筛选器，而其筛选的值为2022/1/1，刚好能够过滤出第一行的数据，所以整列都返回10。

日期主键的特殊行为其实在《DAX权威指南》中也有提到，具体如下：

《DAX权威指南》中给出的解释是：将日期主键作为内部筛选器时，DAX引擎会自动添加 ALL(‘日期主键所在的表’) 作为筛选调节器。因此上面计算列的那个案例中，在设置了日期主键后，计算列的表达式等价于：

列 = CALCULATE(SUM('表'[销量]), '表'[日期]=DATE(2022,1,1))

设置了日期主键后，等价于：

列 = CALCULATE(SUM('表'[销量]), '表'[日期]=DATE(2022,1,1), ALL('表'))

从该解释出发，也可以得到整列都为10的结果，因为行上下文转换后得到的所有筛选器都被ALL调节器移除了，所以最终计值环境也是只有内部筛选器中定义的日期筛选器。

但相比于《DAX权威指南》中自动添加ALL调节器的解释，我还是更倾向于从日期主键筛选器的覆盖范围更大这一个角度去理解，即：日期主键字段上的筛选器可以覆盖日期主键所处的基础表的扩展表上的所有字段的筛选器。因为日期主键筛选器不仅仅只能从CALCULATE的内部筛选器参数中得到，还可以从行上下文转换中得到，比如下面这个例子：

在上面这个例子中，将日期字段作为了迭代函数的第一参数来提供行上下文，并使其发生了行上下文转换以得到日期筛选器。可以看到在设置日期主键的前后，其结果也是明显不一致的。此时若按《DAX权威指南》中自动添加ALL调节器的解释，那么这个ALL调节器应该加在哪里呢，在SUMX第二参数的CALCULATE函数里添加吗，即使是，结果也对不上，如下图所示：

因此，从日期主键筛选器的覆盖范围更大这一个角度去理解反而不会出错，即：日期主键字段上的筛选器可以覆盖日期主键所处的基础表的扩展表上的所有字段的筛选器。

当然，这并不是说《DAX权威指南》中的解释是错误的，只是理解方式上的差异而已，尽可以用自己所能理解的方式去记忆。

自动时间智能功能的应用

自动时间智能功能可以在PowerBI的数据加载设置里打开，如下图所示：

打开自动时间智能选项后，会给各个表中的日期字段自动关联一个位于一端的隐藏的日期表，其中已经被标记为日期表的日期字段、以及作为关系连接字段且属于多端表的日期字段除外，如下图所示：

当某个日期字段关联了自动添加的日期表后，该日期字段的左边会出现一个日期的Icon图标，并且会多出一个日期层次结构，该日期层次结构下的字段即为自动日期表上所拥有的字段，如下图所示：

由于自动添加的各个日期表都是隐藏的，因此无法显式引用其中的字段，必须通过其关联的日期字段来间接引用，引用自动添加的日期表的语法为：'Table'[Date].[隐藏日期表上的字段]，具体用法如下图所示：

由于这个功能隐藏了一些复杂性，一般来说并不建议打开这个功能，特别是新手，很容易就会出现错误，建议还是使用自己创建的日期表。以下是该功能的一些缺点：

• 会给每个日期字段自动关联日期表，若日期字段过多可能会轻微影响性能
• 自动添加的日期表的字段无法自定义，可能不能满足分析需求
• 自动添加的日期表只能关联一个日期字段，无法处理需要关联多个日期字段的场景

下面通过一个例子来介绍该功能带来的一些“复杂性”，如下图所示：

上面的第一个结果就是正常的行上下文转换的结果，由于CALCULATE内部的日期筛选器只能覆盖行上下文转换后的日期筛选器，而无法消除产品与销量的筛选器的影响，导致部分过滤不到数据的行的结果为空。

上面的第二个结果则是打开了自动时间智能选项后的结果，可以看到在计算列的表达式不变的情况下，返回的结果竟然又不一致了。这是因为打开了自动时间智能选项后，日期字段自动关联了一张隐藏的日期表，此时的模型关系如下图所示：

因此当计算列的行上下文转换成筛选上下文时，转换后得到的筛选器不再是只有原来的产品、日期、销量三个筛选器，还多了来自隐藏日期表的各个字段上的筛选器，因为当某个基础表被用作筛选器参数或者该基础表的行上下文被转换成筛选上下文后，其扩展表上不属于基础表自身列的其他列也会设置筛选器。

所以，在未设置日期主键的前提下，计算列中的CALCULATE的内部日期筛选器只能覆盖其相同列上的筛选器，即只能覆盖原来表中的日期筛选器，而无法覆盖来自隐藏日期表上的任何筛选器，所以各个筛选器最终交互后就只有第一行能够正常返回值。以第三行为例，其返回空的计值流程可以表示为如下：

而对于第三个结果，由于设置了日期主键，所以虽然打开了自动时间智能选项，但行上下文转换后多出的隐藏日期表上的筛选器也会被日期主键覆盖，因为日期主键字段上的筛选器可以覆盖日期主键所处的基础表的扩展表上的所有字段的筛选器，故导致最终计值环境就只有CALCULATE的内部筛选器，从而使得整列都返回了10。

从上面例子中应该也可以看到自动时间智能功能所带来的复杂性了，如果不是事先就掌握了该功能的特性，那么很可能就会出现相同表达式下却得到不同结果的情况。特别是自动添加的日期表还是隐藏的，更是加大了排查问题的难度，所以不建议打开这个功能，最佳实践就是使用自己创建的日期表。

连接日期关系时的注意事项

在连接日期关系时还有一个很重要的细节，那就是两个表中连接关系时所使用的日期字段的数据类型要一致，注意并不是显示格式，而是数据类型要一致，否则很可能就会出现参照完整性不匹配的情况。比如说，一个表中的日期字段的数据类型是日期，而另一个表中的日期字段是日期/时间，并且时间部分并不等于0。

下面来看一个帮助理解的例子，其中事实表与日期表的数据，以及模型关系，如下图所示：

初步看起来好像没什么问题，但是在报表视图中随便拉一个矩阵就可以发现问题了，其中日期表的日期字段总是为空，并不能按照日期去分类汇总，如下图所示：

这其实就是因为事实表的日期字段带了时间，但显示格式却没显示时间部分，将显示格式改一下，就可以发现问题了，如下图所示：

由于事实表的日期字段带了时间，而日期表的日期字段并没有带时间，所以导致了参照完整性不匹配，故DAX引擎会在日期表里自动添加一行空行来对应那些不匹配数据，这也就是为什么矩阵中的日期行标签总是为空的原因了。

所以在连接日期关系时最好还是注意一下这个细节，不然很可能就会出错，而且日期字段的显示格式最好还是把时间也显示出来，这可以给排查问题带来帮助。

比如下面这个例子中，仍然使用上文使用过的计算列公式，但计算列返回的结果竟然又多了一种，竟然整列都返回空值了。

这其实是因为日期字段带了时间，而CALCULATE的内部日期筛选器筛选的却是不带时间的日期，因此导致筛选不到内容，故返回空值。当把日期的显示格式完整显示出来后，就能发现问题所在了。

所以另一个最佳实践就是，把日期字段的显示格式完整显示出来。

总结

本篇文章介绍了日期主键的特殊行为，以及应用日期表时的各个注意事项等内容，并且为了帮助理解，还精心设计了一个有趣的案例，即相同的计算列公式在同一个表中却得到了总共四种不同的结果。如果对其中的原理不清楚的话，那么很可能会很惊讶吧。因此这也提醒了我们，在处理日期字段时需要留意各种细节，因为不同的设置或细节可能就会带来不一样的结果。