Excel DAX 处理文本和日期
DAX 可用于涉及处理文本、提取和组合日期和时间值或基于条件创建值的场景。你可以使用 DAX 执行以下操作:
- 通过连接在表中创建一个键列。
- 根据从文本日期中提取的日期部分组成日期。
- 定义自定义日期格式。
-
使用公式更改数据类型。
- 将实数转换为整数。
- 将实数、整数或日期转换为字符串。
- 将字符串转换为实数或日期。
通过连接创建键列
PowerPivot 中的数据模型只允许一个键列。它不支持你可能在外部数据源中找到的复合键。因此,如果数据源中的表中存在任何复合键,则需要将它们组合成数据模型中表的单个键列。
你可以使用 DAX 函数 CONCATENATE 将两列合并为数据模型表中的一列。 DAX 函数 CONCATENATE 将两个文本字符串连接成一个文本字符串。连接的项目可以是文本、数字或表示为文本的布尔值或这些项目的组合。如果列包含适当的值,你也可以使用列引用。
= CONCATENATE ([Column1], [Column2])
DAX CONCATENATE 函数只接受两个参数。如果任何参数不是文本数据类型,它将被转换为文本。 DAX CONCATENATE 函数返回连接的字符串。
Date Based on Date Parts Extracted from a Text Date
Power Pivot 中的数据模型支持日期和时间值的数据类型 datetime。处理日期和/或时间值的 DAX 函数需要参数的日期时间数据类型。
如果你的数据源包含不同格式的日期,你需要首先使用 DAX 公式提取日期部分,然后将这些部分组合起来以构成有效的 DAX 日期时间数据类型。
你可以使用以下 DAX 函数来提取和组合日期:
DATE :以日期时间格式返回指定日期。
日期值 :将文本形式的日期转换为日期时间格式的日期。
时间价值 : 将文本格式的时间转换为日期时间格式的时间。
定义自定义日期格式
假设数据源中的日期不是用标准格式表示的。你可以定义自定义日期格式以确保正确处理值。 DAX FORMAT 函数使你能够根据指定的格式将值转换为文本。
FORMAT (<value>, <format_string>)
FORMAT 函数返回一个字符串,该字符串包含由 format_string 定义的格式化值。
你可以使用预定义的日期和时间格式,也可以为 FORMAT 函数的参数 format_string 创建用户定义的日期和时间格式。
以下是预定义的日期和时间格式名称。如果你使用这些预定义字符串以外的字符串,它们将被解释为自定义日期和时间格式。
S. No. | 格式字符串和说明 |
---|---|
1 |
“一般日期” 显示日期和/或时间。例如,2015 年 2 月 10 日上午 10:10:32 |
2 |
“长日期”或“中日期” 根据长日期格式显示日期。例如,2016 年 3 月 7 日,星期三 |
3 |
《短约会》 使用短日期格式显示日期。例如,2016 年 2 月 3 日 |
4 |
“很久” 使用长时间格式显示时间。 通常包括小时、分钟和秒。 例如,上午 10:10:32 |
5 |
“中等时间” 以 12 小时格式显示时间。 例如,晚上 9:30 |
6 |
“短时间” 以 24 小时格式显示时间。 例如,14:15 |
或者,你可以使用下表中的字符来创建用户定义的日期/时间格式。
S. No. | 人物与描述 |
---|---|
1 |
: 时间分隔符。 时间分隔符。在格式化时间值时分隔小时、分钟和秒。 |
2 |
/ Date separator. 在格式化日期值时分隔日、月和年。 |
3 |
% 用于指示后面的字符应作为单字母格式读取,而不考虑任何尾随字母。也用于指示将单字母格式读取为用户定义的格式。 |
以下是各种角色的详细信息。
-
%d : 将日期显示为不带前导零的数字(例如 5)。
-
%dd : 将日期显示为带有前导零的数字(例如 05)。
-
%ddd : 将日期显示为缩写(例如 Sun)。
-
%dddd : 将日期显示为全名(例如星期日)。
-
%M : 将月份显示为不带前导零的数字(例如一月表示为 1)。
-
%MM : 将月份显示为一个以零开头的数字(例如一月表示为 01)。
-
%MMM : 将月份显示为缩写(例如一月表示为一月)。
-
%MMMM : 将月份显示为完整的月份名称(例如一月)。
-
%gg : 显示句号/纪元字符串(例如 A.D.)。
-
%h : 使用 12 小时制将小时显示为不带前导零的数字(例如下午 1:15:15)。利用 %h 如果这是用户定义的数字格式中的唯一字符。
-
%hh : 使用 12 小时制将小时显示为带前导零的数字(例如 01:15:15 PM)。
-
%H : 使用 24 小时制将小时显示为不带前导零的数字(例如 13:15:15、1:15:15)。如果这是用户定义的数字格式中的唯一字符,请使用 %H。
-
%HH : 使用 24 小时制将小时显示为带前导零的数字(例如 13:15:15、1:15:15)。
-
%m : 将分钟显示为不带前导零的数字(例如 2:1:15)。如果这是用户定义的数字格式中的唯一字符,请使用 %m。
-
%mm : 将分钟显示为带前导零的数字(例如 2:01:15)。
-
%s : 将秒显示为不带前导零的数字(例如 2:15:5)。如果这是用户定义的数字格式中的唯一字符,请使用 %s。
-
%ss : 将秒显示为带有前导零的数字(例如 2:15:05)。
-
%f : 显示秒的分数。例如 ff 显示百分之一秒,而 ffff 显示千分之一秒。你最多可以使用七个 f 用户定义格式的符号。利用 %f 如果这是用户定义的数字格式中的唯一字符。
-
%t : 使用 12 小时制,在中午之前的任何一个小时都显示一个大写的 A;在中午和晚上 11:59 之间的任何时间显示一个大写的 P。如果这是用户定义的数字格式中的唯一字符,请使用 %t。
-
%tt : 对于使用 12 小时制的语言环境,在中午之前的任何小时显示大写 AM;在中午和晚上 11:59 之间的任何时间显示一个大写的 PM。对于使用 24 小时制的语言环境,不显示任何内容。
-
%y : 显示年份编号(0-9),不带前导零。利用 %y 如果这是用户定义的数字格式中的唯一字符。
-
%yy : 以两位数的数字格式显示年份,并以零开头(如果适用)。
-
%yyy : 以四位数字格式显示年份。
-
%yyyy : 以四位数字格式显示年份。
-
%z :显示时区偏移量,不带前导零(例如-8)。利用 %z 如果这是用户定义的数字格式中的唯一字符。
-
%zz : 显示。带前导零的时区偏移量(例如 -08)
-
%zzz :显示完整的时区偏移量(例如-08:00)。
如你所见,格式化字符串区分大小写。通过使用不同的大小写可以获得不同的格式。
更改 DAX 公式输出的数据类型
在 DAX 公式中,输出的数据类型由源列确定,你无法明确指定结果的数据类型。这是因为最佳数据类型由 Power Pivot 确定。但是,你可以使用 Power Pivot 执行的隐式数据类型转换来操作输出数据类型。否则,你可以使用某些 DAX 函数来转换输出数据类型。
使用隐式数据类型转换
-
要将日期或数字字符串转换为数字,请乘以 1.0。例如,= (TODAY()+5)*1.0。此公式计算当前日期加上 5 天,并将结果转换为整数值。
-
要将日期、数字或货币值转换为字符串,请将值与空字符串连接。例如,= Today() & “”
-
使用 DAX 函数进行数据类型转换
你可以将 DAX 函数用于以下用途:
- 将实数转换为整数。
- 将实数、整数或日期转换为字符串。
- 将字符串转换为实数或日期。
你将在以下部分中了解这一点。
将实数转换为整数
你可以使用以下 DAX 函数将实数转换为整数:
ROUND (
天花板(<数字>,<意义>) : 将数字向上舍入到最接近的整数或最接近的有效倍数,并返回一个十进制数。
楼层(<数字>,<意义>) : 将数字向下舍入到零,到最接近的有效倍数并返回一个十进制数。
将实数、整数或日期转换为字符串
你可以使用以下 DAX 函数将实数、整数或日期转换为字符串:
已修复(<数字>、[<小数>]、[
格式(<值>,<格式字符串>) :将一个值按照指定的格式转换为文本。
你已经了解了如何使用 Format 函数将日期转换为字符串。
将字符串转换为实数或日期
你可以使用以下 DAX 函数将字符串转换为实数或日期:
值(<文本>) :将表示数字的文本字符串转换为数字。
日期值(日期文本) :将文本形式的日期转换为日期时间格式的日期。
时间值(time_text) : 将文本格式的时间转换为日期时间格式的时间。