可以编写一个数据分析表达式 (DAX) 公式,向模型添加计算表。 该公式可以复制或转换现有模型数据,以生成一个新表。
备注
计算表不能连接到外部数据;必须使用 Power Query 来完成这个任务。
计算表公式必须返回一个表对象。 最简单的公式可以复制一个现有模型表。
计算表是有成本的:计算表增加了模型的存储大小,并且会延长数据刷新时间。 原因在于,当计算表与刷新表有公式依赖关系时,它们会重新计算。
复制表
以下介绍一个常见设计问题,可通过创建计算表来解决。 首先,应下载并打开 Adventure Works DW 2020 M03.pbix 文件,然后切换到模型关系图。
请注意,在模型图中,Sales 表与 Date 表之间存在三种关系。
![84167a3a7725829efe7d53e5cce40032.png](https://img-blog.csdnimg.cn/img_convert/84167a3a7725829efe7d53e5cce40032.png)
模型关系图显示了三种关系,因为 Sales 表按订单日期、发货日期和截止日期存储销售数据。 如果检查 OrderDateKey、ShipDateKey 和 DueDateKey 列,注意到有一个关系用实线表示,这是活动关系。 用虚线表示的其他关系是非活动关系。
在任意两个模型表之间只能存在一个活动关系。
在关系图中,将光标悬停在活动关系上以突出显示相关列,通过与模型关系图交互来了解相关列。 在本例中,活动关系筛选 Sales 表中的 OrderDateKey 列。 因此,应用于 Date 表的筛选器将传播到 Sales 表以按订单日期进行筛选;它们永远不会按发货日期或截止日期进行筛选。
下一步是删除 Date 表和 Sales 表之间的两个非活动关系。 要删除一个关系,右键单击它,然后在上下文菜单中选择“删除”。 确认删除这两个非活动关系。
接下来,添加一个新表,使报表用户能够按发货日期筛选销售额。 切换到报表视图,然后在“建模”功能区的“计算”组中,选择“新建表”。
![36f5ad2211001e2bdfc94904aa7b7622.png](https://img-blog.csdnimg.cn/img_convert/36f5ad2211001e2bdfc94904aa7b7622.png)
在公式框(位于功能区下方)中,输入以下计算表定义,然后按 Enter。
![dc735ffb6c2dffc1d4854d88f127b972.png](https://img-blog.csdnimg.cn/img_convert/dc735ffb6c2dffc1d4854d88f127b972.png)
计算表定义会复制 Date 表数据,以生成名为 Ship Date 的新表。 Ship Date 表的列和行与 Date 表完全相同。 当 Date 表数据刷新时,Ship Date 表将重新计算,因此它们将始终保持同步。
切换到模型关系图,然后注意到增加了 Ship Date 表。
![d14b055528ee3f07f39c3d0f0e7a62a1.png](https://img-blog.csdnimg.cn/img_convert/d14b055528ee3f07f39c3d0f0e7a62a1.png)
接下来,在 Ship Date 表中的 DateKey 列和 Sales 表中的 ShipDateKey 列之间创建一个关系。 将 Ship Date 表中的 DateKey 列拖动到 Sales 表中的 ShipDateKey 列上即可创建关系。
计算表只复制数据;它不会复制任何模型配置(如列可见性或层次结构)。 如果需要的话,你需要为新表进行相应配置。
可以对计算表的列进行重命名。 在本例中,建议对列进行重命名,使其更好地描述其用途。 例如,可以将 Ship Date 表中的 Fiscal Year 列重命名为 Ship Fiscal Year。 相应地,当在视觉对象中使用 Ship Date 表的字段时,它们的名称会自动包含在标题栏(如视觉对象标题或轴标签)中。
要完成 Ship Date 表的设计,可以执行以下操作:
- 为以下列重命名:
- 将 Date 重命名为 Ship Date
- 将 Fiscal Year 重命名为 Ship Fiscal Year
- 将 Fiscal Quarter 重命名为 Ship Fiscal Quarter
- 将 Month 重命名为 Ship Month
- 将 Full Date 重命名为 Ship Full Date
- 按 Ship Date 列对 Ship Full Date 列进行排序。
- 按 MonthKey 列对 Ship Month 列进行排序。
- 隐藏 MonthKey 列。
- 创建名为“Fiscal”的层次结构,其中包含以下级别:
- Ship Fiscal Year
- Ship Fiscal Quarter
- Ship Month
- Ship Full Date
- 使用 Ship Date 列将 Ship Date 表标记为日期表。
如前面所述,当两个表之间存在多种关系时,计算表非常有用。 你还可以使用计算表向模型添加日期表。 日期表需要应用被称为“时间智能”的特殊时间筛选器。
创建日期表
在下一个示例中,将创建第二个计算表,这次使用 CALENDARAUTO DAX 函数。
使用以下定义创建 Due Date 计算表。
![829e331abe087fb3466e3133d32073cf.png](https://img-blog.csdnimg.cn/img_convert/829e331abe087fb3466e3133d32073cf.png)
CALENDARAUTO 函数采用一个可选参数,即一年的最后一个月份,并返回一个单列表。 如果未传入月份,则假定它为 12(表示 12 月)。 例如,在 Adventure Works,他们的会计年度于每年 6 月 30 日结束,因此,传入的值是 6(表示 6 月)。
该函数扫描模型中的所有日期和日期/时间列,以确定最早和最新存储的日期值。 然后,它会生成一组完整的日期,这些日期跨越模型中的所有日期,确保加载整年的日期。 例如,如果你的模型中存储的最早日期是 2017 年 10 月 15 日,则 CALENDARAUTO 函数返回的第一个日期将是 2017 年 7 月 1 日。 如果模型中存储的最新日期是 2020 年 6 月 15 日,则 CALENDARAUTO 函数返回的最新日期将是 2020 年 6 月 30 日。
实际上,CALENDARAUTO 函数确保可以满足以下要求以标记一个日期表:
- 该表必须包含一个数据类型为 Date 的列。
- 列必须包含完整的年份。
- 列不能缺少日期。
你也可以通过使用 CALENDAR DAX 函数创建日期表。 此函数允许传入两个表示日期范围的日期值。 函数为范围内的每个日期生成一行。 你可以传入静态日期值,也可以传入表达式以从模型中的特定列检索最早/最新日期。
接下来,切换到数据视图,然后在“字段”窗格中,选择“Due Date”表。 现在,查看日期的列。 可能需要通过选择“Date”列标题内的箭头,然后按升序排序,以查看第一行中的最早日期。
排序或筛选列不会改变值的存储方式。 这些功能可帮助你浏览和理解数据。
![83eb32adfed682ef19b650693570b011.png](https://img-blog.csdnimg.cn/img_convert/83eb32adfed682ef19b650693570b011.png)
现在选择了 Date 列,请查看状态栏中的消息(位于左下角)。 它说明了表存储的行数,以及在所选列中发现的非重复值的数目。
![7da53ce50c8f24953ef487b73ccd011a.png](https://img-blog.csdnimg.cn/img_convert/7da53ce50c8f24953ef487b73ccd011a.png)
当表的行数和非重复值的数量相同时,表示列包含唯一值。 这个因素很重要,有两个原因:它满足了标记日期表的要求,并允许将此列用作模型关系中的一方。
每当包含日期列的表刷新时,Due Date 计算表都会重新计算。 换句话说,如果将某一行加载到订单日期为 2020 年 7 月 1 日的 Sales 表中,Due Date 表将自动延长到包括下一年的结束日(2021 年 6 月 30 日)之前的日期。
Due Date 表需要额外的列来支持已知的筛选和分组要求,具体取决于按年、按季度还是按月。
创建计算列
可以编写一个 DAX 公式,向模型中的任意表添加计算列。 计算列公式必须返回标量或单个值。
导入模型中的计算列是有成本的:计算表增加了模型的存储大小,并且会延长数据刷新时间。 原因在于,当计算列与刷新表有公式依赖关系时,它们会重新计算。
在数据视图的“字段”窗格中,确保已选择 Due Date 表。 在创建计算列之前,先将 Date 列重命名为 Due Date。
现在,可以将计算列添加到 Due Date 表中。 若要创建计算列,在“表工具”上下文功能区上,选择“计算”组中的“新建列”。
![e8084486fe337faa953909963557f5b3.png](https://img-blog.csdnimg.cn/img_convert/e8084486fe337faa953909963557f5b3.png)
在编辑框中,输入以下计算列定义,并按 Enter 键。
![abd0bac99aaf26c7038e83ba3995c23e.png](https://img-blog.csdnimg.cn/img_convert/abd0bac99aaf26c7038e83ba3995c23e.png)
计算列定义将 Due Fiscal Year 列添加到 Due Date 表中。 下面的步骤介绍 Microsoft Power BI 如何计算计算列公式:
- 在文本串联运算符 (&) 之前计算加法运算符 (+)。
- YEAR DAX 函数返回截止日期年份的整数值。
- 当截止日期的月份数字为 7-12(7 月到 12 月)时,IF DAX 函数返回值;否则,它将返回 BLANK。 (例如,Adventure Works 的会计年度为 7 月 - 6 月,因此,日历年的最后六个月将使用下一个日历年作为其会计年度。)
- 年份值会添加到 IF 函数返回的值中,该值为 1 或 BLANK。 如果值为 BLANK,则会隐式转换为零 (0),以允许添加以生成会计年度值。
- 文字文本值“FY”与会计年度值连接,将隐式转换为文本。
使用以下定义添加第二个计算列:
![941033698eff2034608a51849522ef36.png](https://img-blog.csdnimg.cn/img_convert/941033698eff2034608a51849522ef36.png)
计算列定义将 Due Fiscal Quarter 列添加到 Due Date 表中。 IF 函数返回季度数字(第 1 季度为 7 月 - 9 月),结果会连接到 Due Fiscal Year 列的值和文字文本 Q。
使用以下定义添加第三个计算列:
![d7ee19feee68360a44111c8c91f05a79.png](https://img-blog.csdnimg.cn/img_convert/d7ee19feee68360a44111c8c91f05a79.png)
计算列定义将 Due Month 列添加到 Due Date 表中。 FORMAT DAX 函数使用格式字符串将 Due Date 列的值转换为文本。 在本例中,格式字符串将生成一个标签,用于说明年份和缩写月份名称。
存在许多用户定义的日期/时间格式。 有关详细信息,请参阅 FORMAT 函数的自定义日期和时间格式 。
使用以下定义添加第四个计算列:
![5bd3b074a2aeb487edd5e4188dcf0fef.png](https://img-blog.csdnimg.cn/img_convert/5bd3b074a2aeb487edd5e4188dcf0fef.png)
使用以下定义添加第五个计算列:
![6783dd1382019c0c8e7c3a18a27dd934.png](https://img-blog.csdnimg.cn/img_convert/6783dd1382019c0c8e7c3a18a27dd934.png)
MonthKey 计算列将截止日期年份乘以值 100,然后加上截止日期的月份数字。 这会生成一个数值,用于按时间顺序对 Due Month 文本值排序。
验证 Due Date 表中是否有六列。 第一列是在创建计算表时添加的,另外五列是作为计算列添加的。
![fa22b6a1a3ce2d89792d6ed88b3a86f0.png](https://img-blog.csdnimg.cn/img_convert/fa22b6a1a3ce2d89792d6ed88b3a86f0.png)
要完成 Due Date 表的设计,可以执行以下操作:
- 按 Due Date 列对 Due Full Date 列进行排序。
- 按 MonthKey 列对 Due Month 列进行排序。
- 隐藏 MonthKey 列。
- 创建名为“Fiscal”的层次结构,其中包含以下级别:
- Due Fiscal Year
- Due Fiscal Quarter
- Due Month
- Due Full Date
- 使用 Due Date 列将 Due Date 表标记为日期表。
了解行上下文
现在你已经创建了计算列,接下来学习如何计算它们的公式。
计算列的公式是针对每一行计算的。 此外,它是在行上下文中进行评估的,即当前行。 来看看“Due Fiscal Year”的计算列定义:
![bb0078e428612bf62d3f40bfdd0b422c.png](https://img-blog.csdnimg.cn/img_convert/bb0078e428612bf62d3f40bfdd0b422c.png)
为每一行计算公式时,'Due Date'[Due Date]
列引用会返回该行的列值。 你会发现,Microsoft Excel 在 Excel 表 中使用公式时也有同样的概念。
不过,行上下文不会超出表的范围。 如果你的公式需要引用其他表中的列,你有两个选项:
- 如果表之间有直接或间接的关系,可以使用 RELATED 或 RELATEDTABLE DAX 函数。 RELATED 函数检索关系的一方的值,RELATEDTABLE 检索多方的值。 RELATEDTABLE 函数返回一个表对象。
- 当表之间没有关系时,可以使用 LOOKUPVALUE DAX 函数。
一般来说,只要有可能,尽量使用 RELATED 函数。 由于关系和列数据的存储和索引方式,它的性能通常会优于 LOOKUPVALUE 函数。
现在,将以下计算列定义添加到 Sales 表中:
![e700a9ea3d1e1ebf4847745aca5050d8.png](https://img-blog.csdnimg.cn/img_convert/e700a9ea3d1e1ebf4847745aca5050d8.png)
计算列定义将“折扣金额”列添加到 Sales 表中。 Power BI 为 Sales 表的每一行进行计算列公式计算。 “订单数量”和“销售额”列的值是在行上下文中检索的。 但是,由于“标价”列属于 Product 表,所以需要使用 RELATED 函数检索销售产品的标价。
当对计算列公式进行计算时,使用行上下文。 当使用一类函数(迭代器函数)时,也会使用行上下文。 使用迭代器函数可以灵活地创建复杂的汇总。 后面的模块中将讨论迭代器函数。
选择一种方法来添加列
可以使用三种方法将列添加到模型表:
- 向视图或表中添加列(作为持久化列),然后在 Power Query 中将它们作为源。 只有当你的数据源是关系数据库,并且你具有执行此操作所需的技能和权限时,此选项才有意义。 不过,这是一个不错的选择,因为它支持将维护简单化,并允许在其他模型或报表中重用列逻辑。
- (使用 M)将计算列添加到 Power Query 查询中。
- (使用 DAX)将计算列添加到模型表中。
无论使用哪种方法,结果都是相同的。 报表用户无法确定列的来源。 通常,他们并不关心列是如何创建的,而是关注列是否能提供正确的数据。
当有多种方法可用于添加列时,可以考虑使用与你的技能最匹配,并且语言(M 或 DAX)支持的方法。 但是,只要有可能,就优先考虑在Power Query中添加计算列,因为它们以更紧凑、更优化的方式加载到模型中。
如果需要向计算表中添加列,请确保创建了一个计算列。 否则,建议仅在计算列公式处于以下情况时使用计算列:
- 依赖于汇总的模型数据。
- 需要使用仅在 DAX 中提供的专用建模函数,如 RELATED 函数或 RELATEDTABLE 函数。 专用函数还可以包括 DAX 上下级层次结构 ,这些层次结构的设计是为了将递归关系自然化为多个列,例如,在 Employee 表中,每一行都存储对经理(同样也是员工)行的引用。