Excel DAX – 处理文本和日期

DAX 可用于涉及处理文本、提取和组合日期和时间值或基于条件创建值的场景。您可以使用 DAX 执行以下操作 –

• 通过串联在表中创建一个键列。
• 根据从文本日期中提取的日期部分组成日期。
• 定义自定义日期格式。
• 使用公式更改数据类型。
  
  • 将实数转换为整数。
  • 将实数、整数或日期转换为字符串。
  • 将字符串转换为实数或日期。

通过串联创建键列

PowerPivot 中的数据模型只允许一个键列。它不支持您可能在外部数据源中找到的复合键。因此，如果数据源的表中存在任何复合键，则需要将它们组合成数据模型中表的单个键列。

您可以使用 DAX 函数 CONCATENATE 将数据模型的表中的两列合并为一列。DAX 函数 CONCATENATE 将两个文本字符串连接成一个文本字符串。连接的项目可以是文本、数字或表示为文本的布尔值或这些项目的组合。如果列包含适当的值，您还可以使用列引用。

= CONCATENATE ([Column1], [Column2]) 

DAX CONCATENATE 函数只接受两个参数。如果任何参数不是文本数据类型，它将被转换为文本。DAX CONCATENATE 函数返回连接的字符串。

基于从文本日期中提取的日期部分的日期

Power Pivot 中的数据模型支持日期和时间值的数据类型 datetime。处理日期和/或时间值的 DAX 函数需要参数的日期时间数据类型。

如果您的数据源包含不同格式的日期，您需要首先使用 DAX 公式提取日期部分并将这些部分组合起来以构成有效的 DAX 日期时间数据类型。

您可以使用以下 DAX 函数来提取和编写日期 –

DATE – 以日期时间格式返回指定的日期。

DATEVALUE – 将文本形式的日期转换为日期时间格式的日期。

TIMEVALUE – 将文本格式的时间转换为日期时间格式的时间。

定义自定义日期格式

假设数据源中的日期没有以标准格式表示。您可以定义自定义日期格式以确保正确处理值。DAX FORMAT 函数使您可以根据指定的格式将值转换为文本。

FORMAT (<value>, <format_string>) 

FORMAT 函数返回一个字符串，其中包含由 format_string 定义的格式化值。

您可以使用预定义的日期和时间格式，也可以为 FORMAT 函数的参数 format_string 创建用户定义的日期和时间格式。

以下是预定义的日期和时间格式名称。如果您使用这些预定义字符串以外的字符串，它们将被解释为自定义日期和时间格式。

或者，您可以使用下表中的字符来创建用户定义的日期/时间格式。

以下是各种角色的详细信息。

• %d – 将日期显示为没有前导零的数字（例如 5）。

• %dd – 将日期显示为带有前导零的数字（例如 05）。

• %ddd – 将日期显示为缩写（例如 Sun）。

• %dddd – 将日期显示为全名（例如星期日）。

• %M – 将月份显示为没有前导零的数字（例如，一月表示为 1）。

• %MM – 将月份显示为带有前导零的数字（例如，一月表示为 01）。

• %MMM – 将月份显示为缩写（例如，一月表示为一月）。

• %MMMM – 将月份显示为完整的月份名称（例如一月）。

• %gg – 显示时期/时代字符串（例如 AD）。

• %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 (<number>, <num_digits>) – 将数字四舍五入到指定的位数并返回一个十进制数。

CEILING (<number>, <significance>) – 将数字向上舍入到最接近的整数或最接近的重要性倍数，并返回一个十进制数。

FLOOR (<number>, <significance>) – 将一个数字向下舍入到零，到最接近的重要性倍数并返回一个十进制数。

将实数、整数或日期转换为字符串

您可以使用以下 DAX 函数将实数、整数或日期转换为字符串 –

FIXED (<number>, [<decimals>], [<no_comma>]) – 舍入一个数字并将结果作为文本返回。小数点右边的位数为 2 或指定的小数位数。结果是带逗号或可选不带逗号。

FORMAT (<value>, <format_string>) – 根据指定的格式将值转换为文本。

您已经了解了如何使用 Format 函数将日期转换为字符串。

将字符串转换为实数或日期

您可以使用以下 DAX 函数将字符串转换为实数或日期 –

VALUE (<text>) – 将表示数字的文本字符串转换为数字。

DATEVALUE (date_text) – 将文本形式的日期转换为日期时间格式的日期。

TIMEVALUE (time_text) – 将文本格式的时间转换为日期时间格式的时间。