Excel Dax 简明教程

Excel DAX - Operators

DAX 是一种由函数、运算符和值组成的公式语言，可以用于公式或表达式中，以计算并返回一个或多个值。

DAX is a formula language comprising of functions, operators, and values that can be used in a formula or expression, to calculate and return one or more values.

您可以使用 DAX operators 来比较值、执行算术计算和连接字符串。在本章中，您将了解 DAX 运算符以及如何使用它们。

You can use DAX operators to compare values, perform arithmetic calculations, and concatenate strings. In this chapter, you will learn about DAX operators and how to use them.

Types of DAX Operators

DAX 支持以下类型的运算符：

DAX supports the following types of operators −

  1. DAX Arithmetic Operators

  2. DAX Comparison Operators

  3. DAX Text Concatenation Operator

  4. DAX Logical Operators

DAX Operator Precedence Order

可以使用一个包含多个 DAX 运算符的 DAX 公式，来组合多个值或表达式。在这种情况下，最终结果将取决于执行运算的顺序。DAX 提供了默认运算符优先级顺序以及替代默认优先级顺序的方法。

You can have a DAX formula with many DAX operators combining several values or expressions. In such a case, the final result will depend on the order in which the operations are performed. DAX provides you with the default operator precedence order and also ways of overriding the default precedence order.

DAX 默认运算符优先级列在以下表格中。

DAX default operator precedence is listed in the following table.

Precedence Order

Operator(s)

Operation

1

^

Exponentiation

2

–

Sign

3

* and /

Multiplication and Division

4

!

NOT

5

+ and –

Addition and Subtraction

6

&

Concatenation

7

=, <, >, ⇐, >= and <>

Equal to, Less than, Greater than, Less than or equal to, Greater than or equal to and Not equal to

DAX Expression Syntax

您需要首先了解 DAX 表达式语法以及如何使用操作数和运算符对表达式进行求值。

You need to first understand the DAX expression syntax and how the expression evaluation is done with the operands and operators.

  1. All expressions always begin with an equal sign (=). The equal sign indicates that the succeeding characters constitute an expression.

  2. To the right of the equal sign, you will have the operands connected by the DAX operators. For example, = 5 + 4 > 5. = 5 * 6 - 3.

  3. Expressions are always read from left to right, and the calculations are done in that sequence, based on the DAX operator precedence given in the previous section.

  4. If the DAX operators have equal precedence value, they are evaluated from the left to right. For example, =5*6/10. Both * and / have same the precedent order. Hence, the expression is evaluated as 30/10 = 3.

  5. If the DAX operators in the expression have different precedence values, then they are evaluated in the precedence order from the left to right. = 5 + 4 > 7. Default precedence is + first and > next. Hence, the expression is calculated from the left to right. - 5 + 4 is calculated first resulting in 9 and then 9 > 5 is evaluated that results in TRUE. = 5 * 6 - 3. Default precedence is * first and - next. Hence, the expression is calculated from the left to right. - 5 * 6 is calculated first resulting in 30 and then 30 - 3 is calculated that results in 27. = 2 * 5 - 6 * 3. Default precedence is * first, * next and then -. Hence, the expression evaluates as 10 – 18 and then as -8. Note, that it is not 10 - 6 resulting in 4 and then 4*3 that is 12.

Using Parentheses to Control DAX Calculation Order

您可以使用括号来更改 DAX 默认运算符优先级顺序，对操作数和运算符进行分组以控制计算顺序。

You can change the DAX default operator precedence order by using parentheses, grouping the operands and the operators to control the calculation sequence.

例如，= 5 * 6 - 3 在 DAX 默认运算符优先级顺序下计算结果为 27。如果您使用括号对操作数和运算符进行分组，如 = 5 * (6 - 3)，则首先计算 6 - 3，结果为 3，然后计算 5 * 3，结果为 15。

For example, = 5 * 6 - 3 evaluates to 27 with the DAX default operator precedence order. If you use parenthesis to group the operands and operators as = 5 * (6 - 3), then 6 - 3 is calculated first resulting in 3 and then 5 * 3 is calculated which results in 15.

2 * 5 - 6 * 3 evaluates to -8 with the DAX default operator precedence order. If you use parenthesis to group the operands and operators as = 2 * (5 - 6) * 3, then 5 - 6 is calculated first resulting in -1 and then 2 * (-1) * 3 is calculated which results in -6.

如您所见，对于相同的操作数和运算符，可以通过分组方式得到不同的结果。因此，当您在 DAX 公式中使用 DAX 运算符时，您应该注意计算顺序。

As you can see, with the same operands and operators, different results are possible by the way you group them. Hence, when you use the DAX operators in the DAX formulas, you should pay attention to how the computation sequence is to be.

Differences Between Excel and DAX

虽然 DAX 与 Excel 公式有相似之处，但两者之间存在一些重要的差异。

Though DAX has similarities with Excel formulas, there are certain significant differences between the two.

  1. DAX is more powerful than Excel because of its underlying memory resident computation engine.

  2. DAX supports more data types than Excel.

  3. DAX provides additional advanced features of a relational database, Data Model, including richer support for date and time types.

在某些情况下，DAX 中的计算结果或函数行为可能与 Excel 中的不相同。这是由于以下差异造成的：

In some cases, the results of calculations or the behavior of functions in DAX may not be the same as in Excel. This is due to the differences in the following −

  1. Data type casting

  2. Data types

Difference in Data Type Casting

在 DAX 中，当您有表达式 =value1 operator value2 时，两个操作数 value1 和 value2 应为相同的数据类型。如果数据类型不同，DAX 会首先将它们隐式地转换为公共数据类型。有关详细信息，请参阅章节 - DAX 语法。

In DAX, when you have an expression =value1 operator value2, the two operands value1 and value2 should be of the same data type. If the data types are different, DAX will convert them first to a common data type implicitly. Refer to the chapter – DAX Syntax for details.

例如，您必须比较不同数据类型两个操作数，比如说某个公式产生的数字，如 =[Amount] * 0.08 和一个整数。第一个数字可以是有许多小数位的小数，而第二个数字是一个整数。然后，DAX 会这样处理：

For example, you have to compare two operands of different data types, say a number resulting from a formula, such as =[Amount] * 0.08 and an integer. The first number can be a decimal number with many decimal places, whereas the second number is an integer. Then DAX handles it as follows −

  1. First, DAX will convert both the operands to real numbers using the largest numeric format that can store both kinds of numbers.

  2. Next, DAX will compare the two real numbers.

相反，Excel 会尝试比较不同数据类型的值，而不用首先将这些值强制转换为公共数据类型。由于此原因，您可能会发现在 DAX 和 Excel 中，同一个比较表达式的结果不同。

In contrast, Excel tries to compare values of different data types without first coercing them to a common data type. For this reason, you might find different results in DAX and in Excel for the same comparison expression.

Difference in Data Types

DAX 和 Excel 中的操作符优先级顺序相同。但是，DAX 不支持 Excel 支持的百分号 (%) 操作符和数据范围。此外，DAX 支持表作为数据类型，而这在 Excel 中不行。

The operator precedence order in DAX and Excel is the same. However, the operator percent (%) and data ranges that Excel supports are not supported by DAX. Moreover, DAX supports table as a data type, which is not the case in Excel.

此外，在 Excel 公式中，您可以引用一个单元格，或一个数组或一系列单元格。在 DAX 公式中，您不能引用任何这些。DAX 公式引用到数据时应采用表、列、计算字段和计算列的方式。

Further, in Excel formulas, you can refer to a single cell, or an array or a range of cells. In DAX formulas, you cannot refer to any of these. The DAX formula references to data should be by tables, columns, calculated fields, and calculated columns.

如果您从 Excel 复制公式并将其粘贴在 DAX 中，确保 DAX 公式的正确性，因为 DAX 语法不同于 Excel 公式语法。此外，即使一个函数在 DAX 和 Excel 中具有相同名称，其参数也可能不同，函数的结果也可能不同。

If you copy formulas from Excel and paste them in DAX, ensure the correctness of the DAX formula as DAX syntax is different from Excel formula syntax. Also, even if a function has the same name in DAX and Excel, its parameters might be different and the result of the function can also be different.

您将在后面的章节中了解有关所有这些内容的更多信息。

You will learn more about all these in the subsequent chapters.