Excel Dax 简明教程

Excel DAX - Working with Text and Dates

DAX 可用于涉及使用文本、提取和组合日期时间值或根据条件创建值的场景中。你可以使用 DAX 做以下事情 -

DAX can be used in the scenarios involving working along with text, extracting and composing date and time values or creating values based on a condition. You can do the following with DAX −

  1. Create a key column in a table by concatenation.

  2. Compose a date based on date parts extracted from a text date.

  3. Define a custom date format.

  4. Change data types using a formula. Convert real numbers to integers.Convert real numbers, integers, or dates to strings.Convert strings to real numbers or dates.

Creating a Key Column by Concatenation

PowerPivot 中的数据模型仅允许使用一个键列。它不支持可能在外部数据源中找到的复合键。因此，如果数据源中表中存在复合键，你需要将它们合并到数据模型中表中的一个键列中。

Data Model in PowerPivot allows only a single key column. It does not support composite keys that you might find in the external data sources. Hence, if any composite keys exist in a table in the data source, you need to combine them into a single key column for the table in the Data Model.

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

You can use the DAX function CONCATENATE to combine two columns into a single column in a table in the Data Model. DAX function CONCATENATE joins two text strings into one text string. The joined items can be text, numbers, or Boolean values represented as text or a combination of those items. You can also use a column reference if the column contains appropriate values.

= CONCATENATE ([Column1], [Column2])

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

DAX CONCATENATE function accepts only two arguments. If any of the arguments is not of text data type, it will be converted to text. DAX CONCATENATE function returns the concatenated string.

Date Based on Date Parts Extracted from a Text Date

Power Pivot 中的数据模型支持对日期和时间值使用数据类型 datetime。对日期和/或时间值起作用的 DAX 函数需要对参数使用 datetime 数据类型。

Data Model in Power Pivot supports a data type datetime for date and time values. The DAX functions that work on date and/or time values require the datetime data type for the arguments.

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

If your data source contains dates of a different format, you need to first extract the date parts using a DAX formula and combine those parts to constitute a valid DAX datetime data type.

你可以使用以下 DAX 函数来提取和组合日期：

You can use the following DAX functions to extract and compose dates −

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

DATE − Returns the specified date in datetime format.

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

DATEVALUE − Converts a date in the form of text to a date in datetime format.

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

TIMEVALUE − Converts a time in text format to a time in datetime format.

Defining a Custom Date Format

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

Suppose the dates in your data source are not represented by a standard format. You can define a custom date format to ensure that the values are handled correctly. DAX FORMAT function enables you to convert a value to text according to the specified format.

FORMAT (<value>, <format_string>)

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

FORMAT function returns a string containing value formatted as defined by format_string.

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

You can either use pre-defined Date and Time formats or you can create user-defined Date and Time formats for the argument format_string of the FORMAT function.

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

Following are the predefined date and time format names. If you use strings other than these predefined strings, they will be interpreted as a custom date and time format.

S. No.

Format_String & Description

1

"General Date" Displays a date and/or time. For example, 2/10/2015 10:10:32 AM

2

"Long Date" or "Medium Date" Displays a date according to long date format. For example, Wednesday, March 07, 2016

3

"Short Date" Displays a date using short date format. For example, 2/03/2016

4

"Long Time" Displays a time using long time format. Typically includes hours, minutes and seconds. For example, 10:10:32 AM

5

"Medium Time" Displays a time in 12-hour format. For example, 09:30 PM

6

"Short Time" Displays a time in 24-hour format. For example, 14:15

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

Alternatively, you can use the characters in the following table to create user-defined date/time formats.

S. No.

Character & Description

1

: Time separator. Time separator. Separates hours, minutes, and seconds when time values are formatted.

2

/ Date separator. Separates the day, month, and year when date values are formatted.

3

% Used to indicate that the following character should be read as a single-letter format without regard to any trailing letters. Also used to indicate that a single-letter format is read as a userdefined format.

以下是各个字符的详细信息。

Following are the details of the various characters.

  1. %d − Displays the day as a number without a leading zero (e.g. 5).

  2. %dd − Displays the day as a number with a leading zero (e.g. 05).

  3. %ddd − Displays the day as an abbreviation (e.g. Sun).

  4. %dddd − Displays the day as a full name (e.g. Sunday).

  5. %M − Displays the month as a number without a leading zero (e.g. January is represented as 1).

  6. %MM − Displays the month as a number with a leading zero (e.g. January is represented as 01).

  7. %MMM − Displays the month as an abbreviation (e.g. January is represented as Jan).

  8. %MMMM − Displays the month as a full month name (e.g. January).

  9. %gg − Displays the period/era string (e.g. A.D.).

  10. %h − Displays the hour as a number without leading zeros using the 12-hour clock (e.g. 1:15:15 PM). Use %h if this is the only character in your user-defined numeric format.

  11. %hh − Displays the hour as a number with leading zeros using the 12-hour clock (e.g. 01:15:15 PM).

  12. %H − Displays the hour as a number without leading zeros using the 24-hour clock (e.g. 13:15:15, 1:15:15). Use %H if this is the only character in your user-defined numeric format.

  13. %HH − Displays the hour as a number with leading zeros using the 24-hour clock (e.g. 13:15:15, 1:15:15).

  14. %m − Displays the minute as a number without leading zeros (e.g. 2:1:15). Use %m if this is the only character in your user-defined numeric format.

  15. %mm − Displays the minute as a number with leading zeros (e.g. 2:01:15).

  16. %s − Displays the second as a number without leading zeros (e.g. 2:15:5). Use %s if this is the only character in your user-defined numeric format.

  17. %ss − Displays the second as a number with leading zeros (e.g. 2:15:05).

  18. %f − Displays fractions of seconds. For e.g. ff displays hundredths of seconds, whereas ffff displays ten-thousandths of seconds. You can use up to seven f symbols in your user-defined format. Use %f if this is the only character in your user-defined numeric format.

  19. %t − Uses the 12-hour clock and displays an uppercase A for any hour before noon; displays an uppercase P for any hour between noon and 11:59 P.M. Use %t if this is the only character in your user-defined numeric format.

  20. %tt − For locales that use a 12-hour clock, displays an uppercase AM with any hour before noon; displays an uppercase PM with any hour between noon and 11:59 P.M. For locales that use a 24-hour clock, displays nothing.

  21. %y − Displays the year number (0-9) without leading zeros. Use %y if this is the only character in your user-defined numeric format.

  22. %yy − Displays the year in two-digit numeric format with a leading zero, if applicable.

  23. %yyy − Displays the year in four-digit numeric format.

  24. %yyyy − Displays the year in four-digit numeric format.

  25. %z − Displays the timezone offset without a leading zero (e.g. -8). Use %z if this is the only character in your user-defined numeric format.

  26. %zz − Displays the. timezone offset with a leading zero (e.g. -08)

  27. %zzz − Displays the full timezone offset (e.g. -08:00).

正如您所观察到的，格式化字符串区分大小写。可以通过使用不同的情况来获取不同的格式。

As you can observe, formatting strings are case sensitive. Different formatting can be obtained by using a different case.

Changing Data Types of DAX Formula Outputs

在 DAX 公式中，输出的数据类型由源列决定，并且您不能明确指定结果的数据类型。这是因为最佳的数据类型由 Power Pivot 决定。然而，您可以使用 Power Pivot 执行的隐式数据类型转换来操作输出数据类型。否则，可以使用某些 DAX 函数转换输出数据类型。

In DAX formulas, the data type of the output is determined by the source columns and you cannot explicitly specify the data type of the result. This is because the optimal data type is determined by Power Pivot. However, you can use the implicit data type conversions performed by Power Pivot to manipulate the output data type. Otherwise, you can use certain DAX functions to convert the output data type.

Using the Implicit Data Type Conversions

Using the Implicit Data Type Conversions

  1. To convert a date or a number string to a number, multiply by 1.0. For example, = (TODAY()+5)*1.0. This formula calculates the current date plus 5 days and converts the result to an integer value. To convert a date, number or currency value to a string, concatenate the value with an empty string. For example, = Today() & “”

Using the DAX Functions for Data Type Conversions

Using the DAX Functions for Data Type Conversions

您可以将 DAX 函数用于以下内容 −

You can use DAX functions for the following −

  1. Converting Real Numbers to Integers.

  2. Converting Real Numbers, Integers or Dates to Strings.

  3. Converting Strings to Real Numbers or Dates.

您将在以下部分学习这一点。

You will learn this in the following sections.

Converting Real Numbers to Integers

您可以使用以下 DAX 函数将实数转换成整数−

You can use the following DAX functions for converting real numbers to integers −

ROUND (<number>, <num_digits>) − 将数字舍入到指定位数，并返回一个十进制数。

ROUND (<number>, <num_digits>) − Rounds a number to the specified number of digits and returns a decimal number.

CEILING (<number>, <significance>) − 将数字向上舍入，至最接近的整数或最接近的显着倍数，并返回一个十进制数。

CEILING (<number>, <significance>) − Rounds a number up, to the nearest integer or to the nearest multiple of significance and returns a decimal number.

FLOOR (<number>, <significance>) − 将数字向下舍入（舍入到零），至最接近的显着倍数，并返回一个十进制数。

FLOOR (<number>, <significance>) − Rounds a number down, toward zero, to the nearest multiple of significance and returns a decimal number.

Converting Real Numbers, Integers, or Dates to Strings

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

You can use the following DAX functions for converting real numbers, integers, or dates to strings −

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

FIXED (<number>, [<decimals>], [<no_comma>]) − Rounds a number and returns the result as text. The number of digits to the right of the decimal point is 2 or the specified number of decimals. The result is with commas or optionally with no commas.

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

FORMAT (<value>, <format_string>) − Converts a value to text according to the specified format.

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

You have already learnt about using Format function for converting dates to strings.

Converting Strings to Real Numbers or Dates

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

You can use the following DAX functions for converting strings to real numbers or dates −

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

VALUE (<text>) − Converts a text string that represents a number to a number.

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

DATEVALUE (date_text) − Converts a date in the form of text to a date in datetime format.

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

TIMEVALUE (time_text) − Converts a time in text format to a time in datetime format.