从数据到信息
从信息到洞察

关闭自动日期和标记日期表

本文介绍在使用时间智能函数之前需要做的两项准备工作,正确的设置它们可以帮助你建立正确的使用习惯,少走弯路

自动日期/时间

Power BI 有一个叫作「自动日期/时间」的特性,可以通过「选项 – 数据加载」进行配置,如图所示:

「自动日期/时间」默认开启,Power BI 会自动为模型中的每个日期或日期/时间格式的列创建一个日期表,这个日期表包含年份、季度、月份和日期维度,用户可以使用这些维度对日期进行切片。这种自动创建的表对用户是隐藏的,在模型中不可见,而且也不能修改。在自动创建日期表的原理一文中,你可以了解背后的原理。

为什么要关闭自动日期/时间

  1. Power BI Desktop 为每个日期列生成一个表。这会在模型中创建大量无关联的日期表。你很难使用其中任意一个进行多个表的关联分析,比如在同一个矩阵中构建同时显示订购数量和销售数量的简单报表。
  2. 这些表是隐藏的,你不能按自己的需要修改。因此,如果你想添加一列来标记工作日,是不可能的。
  3. 自动创建的日期表位于关系的一端,如果你在对关系多端的表进行计算时引入了扩展表,这时由于隐式日期表的介入,可能会得到意外的结果。

关于第三点,我们来看下面这个案例,模型只有一个表,我们在这个表中创建一列,计算每种产品按销量大小从低到高的累计销量,公式的写法没有任何问题,却没有得到的预期的结果,原因就在于隐藏的日期表。当然,如果你将公式中任意一个 ALL 的参数改为整个表,也可以得到正确的结果,但这里我们的真正目的是理解此写法的错误原因。

请先思考五分钟,再阅读解析

由于 CALCULATE 的作用,当前行所在的行上下文被转换为筛选上下文,因为转换是对整行进行的,转换后的筛选上下文是整个表的子集,基于扩展表原理,所有位于这个表关系一端的表也同时被筛选,隐藏的日期表就这样参与了筛选。而上面的写法未排除来自日期表的筛选器,所以每行仍然只计算当前行的结果。关闭「自动日期/时间」后,结果就会恢复正常。

如果你好奇基于日期列创建的关系,在对日期列进行筛选时为什么没有自动添加 ALL(Date)作为筛选器,原因是上图的表位于关系的多端,自动添加 ALL 的行为只对一端表的日期列有效。

综上所述,建议你永久关闭「自动日期/时间」,文章开头的截图演示了在当前文件中关闭「自动日期/时间」的方法,永久关闭的方法是在「选项 – 全局 – 数据加载」中关闭「新文件的自动日期/时间」

标记日期表

标记为日期表是使用时间智能函数的一项准备工作,Excel 和 Power BI 都提供标记为日期表的功能,这里将介绍标记日期表的原因以及如何使用等效方案

表间关系在大部分情况下都是通过日期列定义的,时间智能函数可以在这种情况下正常工作,因为时间智能函数对日期表的日期列应用筛选器,自动覆盖日期表其他列的筛选条件。这也是为什么没有标记日期表,公式依然可以正常计值的原因。

什么情况下需要标记日期表

但是在某些情况下,你的日期表可能会基于非日期列建立关系,比如有的日期表使用整数列建立关系,例如示例中的 Date[DateKey]。

Sales 和 Date 表之间的关系从 Date 列(日期类型),变更为 DateKey 列(整数类型)

DateKey 列包含格式为 YYYYMMDD 的整数值(例如,20080120 表示 2008 年 1 月 20 日),在这种情况下,标记为日期表的特性必须在日期表中处于激活状态,否则 Date[DateKey]列上的筛选器不会覆盖日期表上其他现有的活动筛选器。例如,考虑数据透视表如图 7-14 所示:度量值 Sales Amount Jan-Feb 2008 Single Column 没有达到预期的效果,因为它总是返回与初始度量值 Sale Amount 相同的值,只筛选 2008 年 1 月和 2 月的数据(如果外部上下文中不包括这些日期则返回空白)。

图 7-14 Sales Amount Jan-Feb 2008 Single Column 不会将所选内容扩展到报表中的行选择之外的日期,因为它不会替换年份和月份的现有筛选器

这种现象的原因是日期表上有其他活动的筛选器,来自于数据透视表的行标签。例如,1 月和 2 月的行只显示每个月的值,而不是两者的总和,其他月份返回空白值,而并非无论选择任何日期都相同的重复值,

图 7-12 Sales Amount Jan-Feb 2008 始终显示 2008 年 1 月和 2 月的总和, 而不考虑行上的日期范围选择

如图 7-12 所示。如果你启用标记为日期表特性,并且指定日期列为’Date'[Date],那么当 CALCULATECALCULATETABLE 函数的筛选器参数使用来自这个日期列的筛选器时,DAX 将自动从日期表的所有其他列中删除筛选器。(这样做之后,你将得到与图 7-12 中相同的结果。)

考虑以下公式模板:

CALCULATE (
           <expr>,
           'Date'[Date] >= DATE ( 2008, 1, 1 )
            && 'Date'[Date] <= DATE ( 2008, 2, 29 ),
            <filter2>,
            ...
            <filterN>
)

对于使用整数列与模型建立关系的日期表,借助标记为日期表功能将关系列重新指定为日期类型的列之后,对于使用日期列作为筛选器参数的公式,DAX 将使用与下面的公式相同的技术移除相同表的任何已有筛选器:

CALCULATE (
           <expr>,
           'Date'[Date] >= DATE ( 2008, 1, 1 )
            && 'Date'[Date] <= DATE ( 2008, 2, 29 ),
            <filter2>,
            ...
            <filterN>,
            ALL ( 'Date' )
)

只有当你正确的使用了标记为日期表这个特性后,时间智能函数才会正常工作。

如果想在没有设置此特性的模型中定义与时间计算相关的度量值,你可以在 CALCULATE 函数中放置 ALL (Date)作为筛选器参数,用来移除那些可能影响最终结果的,来自日期表其他列的筛选器

如果你定义这样一个筛选器参数,它迭代整个日期表而不是单列,新的日期筛选器将覆盖日期表上的任何现有的筛选上下文,ALL (Date)也就不再需要了。例如,你可以用这种方式编写前面的公式模板,这样即使标记为日期表的设置未激活,公式仍然可以正常工作:

CALCULATE (
           <expr>,
           FILTER (
                   ALL ( 'Date' ),
                   'Date'[Date] >= DATE ( 2008, 1, 1 )
                    && 'Date'[Date] <= DATE ( 2008, 2, 29 ),
           ),
           <filter2>,
            ...
           <filterN>
)

标记为日期表可以自动移除来自日期表其他列的筛选器,这种特性非常重要。在 Excel 和 Power BI Desktop 中,你都可以找到标记为日期表功能。

说点什么

1000
 
鼓掌微笑开心憧憬爱你色并不觉得吃瓜doge二哈喵喵思考笑哭捂脸悲伤大哭抓狂汗偷笑打脸捂眼黑线问号晕拜拜闭嘴衰咒骂ok作揖
  订阅本文评论  
提醒
DAX 圣经

导读

初识 DAX

DAX 基础知识

DAX 原理

DAX 高级原理

基础函数类型

迭代函数

CALCULATE 函数

CALCULATE 调节器

基础表函数

条件判断函数

查找匹配函数

时间智能函数

统计类函数

投影函数

分组/连接函数

集合函数

其他函数