Excel Dax 简明教程
Excel DAX - Working with Text and Dates
DAX 可用于涉及使用文本、提取和组合日期时间值或根据条件创建值的场景中。你可以使用 DAX 做以下事情 -
-
通过连接来创建一个表中的键列。
-
基于从文本日期中提取的日期部分组合日期。
-
定义自定义日期格式。
-
使用公式更改数据类型。将实数转换为整数。将实数、整数或日期转换为字符串。将字符串转换为实数或日期。
Creating a Key Column by Concatenation
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 函数需要对参数使用 datetime 数据类型。
如果数据源包含不同格式的日期,你需要首先使用 DAX 公式提取日期部分,并将这些部分组合以构成一个有效的 DAX 日期时间数据类型。
你可以使用以下 DAX 函数来提取和组合日期:
DATE - 返回日期时间格式的指定日期。
DATEVALUE - 将文本格式的日期转换为日期时间格式的日期。
TIMEVALUE - 将文本格式的时间转换为日期时间格式的时间。
Defining a Custom Date Format
假设数据源中的日期未以标准格式表示。你可以定义自定义日期格式,以确保正确处理值。DAX FORMAT 函数使你能够根据指定格式将值转换为文本。
FORMAT (<value>, <format_string>)
FORMAT 函数返回一个字符串,其中包含按 format_string 定义的格式化值。
你可以使用预定义的日期和时间格式,也可以为 FORMAT 函数的参数 format_string 创建用户定义的日期和时间格式。
以下是预定义的日期和时间格式名称。如果你使用除以下这些预定义字符串之外的字符串,它们将被解释为自定义日期和时间格式。
S. No. |
Format_String & Description |
1 |
"General Date" 显示日期和/或时间。例如,2/10/2015 10:10:32 AM |
2 |
"Long Date" or "Medium Date" 根据长日期格式显示日期。例如,2016 年 3 月 7 日星期三 |
3 |
"Short Date" 使用短日期格式显示日期。例如,2/03/2016 |
4 |
"Long Time" 使用长时间格式显示时间。通常包括小时、分钟和秒。例如,上午 10:10:32 |
5 |
"Medium Time" 使用 12 小时格式显示时间。例如,下午 09:30 |
6 |
"Short Time" 使用 24 小时格式显示时间。例如,14:15 |
或者,你可以使用下表中的字符来创建用户定义的日期/时间格式。
S. No. |
Character & Description |
1 |
: 时间分隔符。时间分隔符。在格式化时间值时,分隔小时、分钟和秒。 |
2 |
/ 日期分隔符。在格式化日期值时,分隔日、月和年。 |
3 |
% 用于指示应将以下字符当作单字母格式来读,而不考虑任何尾随字母。还用于指示将单字母格式读作用户定义的格式。 |
以下是各个字符的详细信息。
-
%d − 显示日,没有前导零(例如 5)。
-
%dd − 显示日,有前导零(例如 05)。
-
%ddd − 显示日的缩写(例如星期日)。
-
%dddd − 显示日的全名(例如星期日)。
-
%M − 显示月,没有前导零(例如 1 月表示为 1)。
-
%MM − 显示月,有前导零(例如 1 月表示为 01)。
-
%MMM − 显示月的缩写(例如 1 月表示为 1 月)。
-
%MMMM − 显示月的全名(例如 1 月)。
-
%gg − 显示期限/时代字符串(例如公元)。
-
%h − 显示小时,没有前导零,使用 12 小时制(例如下午 1:15:15)。如果这是用户定义的数字格式中的唯一字符,请使用 %h 。
-
%hh − 显示小时,有前导零,使用 12 小时制(例如下午 01:15:15)。
-
%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 显示一万分之一秒。你可以在用户自定义的格式中使用最多 7 个 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)。
正如您所观察到的,格式化字符串区分大小写。可以通过使用不同的情况来获取不同的格式。
Changing Data Types of DAX Formula Outputs
在 DAX 公式中,输出的数据类型由源列决定,并且您不能明确指定结果的数据类型。这是因为最佳的数据类型由 Power Pivot 决定。然而,您可以使用 Power Pivot 执行的隐式数据类型转换来操作输出数据类型。否则,可以使用某些 DAX 函数转换输出数据类型。
Using the Implicit Data Type Conversions
-
要将日期或数字字符串转换为数字,请乘以 1.0。例如,=(TODAY()+5)*1.0。此公式计算当前日期加上 5 天,并将结果转换为整数值。要将日期、数字或货币值转换为字符串,请将该值与空字符串连接。例如,=Today() & “”
Using the DAX Functions for Data Type Conversions
您可以将 DAX 函数用于以下内容 −
-
将实数转换为整数。
-
将实数、整数或日期转换为字符串。
-
将字符串转换为实数或日期。
您将在以下部分学习这一点。
Converting Real Numbers to Integers
您可以使用以下 DAX 函数将实数转换成整数−
ROUND (<number>, <num_digits>) − 将数字舍入到指定位数,并返回一个十进制数。
CEILING (<number>, <significance>) − 将数字向上舍入,至最接近的整数或最接近的显着倍数,并返回一个十进制数。
FLOOR (<number>, <significance>) − 将数字向下舍入(舍入到零),至最接近的显着倍数,并返回一个十进制数。