Excel Dax 简明教程

Conditional Values and Testing for Errors

您可以使用 DAX 函数测试数据中的值，并根据条件得到不同的值。例如，您可以测试年销售额，并根据结果，将经销商标注为首选或价值。

You can use DAX functions to test the values in the data that result in different values based on a condition. For e.g., you can test the yearly sales amount and based on the result, label resellers either as Preferred or Value.

您还可以使用 DAX 函数检查值范围或类型，以防止意外数据错误中断计算。

You can also use DAX functions for checking the range or the type of values, to prevent unexpected data errors from breaking calculations.

Creating a Value Based on a Condition

您可以使用嵌套的“IF”条件来测试值和有条件地生成新值。以下 DAX 函数适用于条件处理和条件值−

You can use nested IF conditions to test values and generate new values conditionally. Following DAX functions are useful for conditional processing and conditional values −

IF (<logical_test>,<value_if_true>, [<value_if_false>]) − 检查是否满足一个条件。如果条件为真，则返回一个值；如果条件为假，则返回另一个值。“Value_if_false”是可选的，如果省略且条件为假，则该函数返回 BLANK ()。

IF (<logical_test>,<value_if_true>, [<value_if_false>]) − Checks if a condition is met. Returns one value if the condition is TRUE and returns another value if the condition is FALSE. Value_if_false is optional, and if omitted and the condition is FALSE, the function returns BLANK ().

OR (<logical1>,<logical2>) − 检查其中一个参数是否为真来返回真。如果两个参数都为假，则该函数返回假。

OR (<logical1>,<logical2>) − Checks whether one of the arguments is TRUE to return TRUE. The function returns FALSE if both arguments are FALSE.

CONCATENATE (<text1>, <text2>) − 将两个文本字符串连接成一个文本字符串。连接项可以是文本、数字或表示为文本的布尔值或这些项的组合。如果该列包含适当的值，您还可以使用列引用。

CONCATENATE (<text1>, <text2>) − 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.

Testing for Errors within a DAX Formula

在 DAX 中，您不能在一行的计算列中具有有效值，而在另一行中具有无效值。也就是说，如果计算列的任何部分发生错误，则整个列都会被标记为错误，您必须更正 DAX 公式以消除导致无效值的错误。

In DAX, you cannot have valid values in one row of a calculated column and invalid values in another row. That is, if there is an error in any part of a calculated column, the entire column is flagged with an error and you must correct the DAX formula to remove the errors that result in invalid values.

DAX 公式中的一些常见错误：

Some common errors in DAX formulas are −

  1. Division by zero.

  2. Argument to a function is blank while the expected argument is numeric value.

您可以结合使用逻辑函数和信息函数来测试错误，并始终返回有效值，以避免在计算列中返回错误。以下 DAX 函数可以帮助您解决此问题。

You can use a combination of logical and information functions to test for errors and always return valid values to avoid returning errors in a calculated column. Following DAX functions help you in this.

ISBLANK (<value>) - 检查值是否为空白，并返回 TRUE 或 FALSE。

ISBLANK (<value>) − Checks whether a value is blank and returns TRUE or FALSE.

IFERROR (value, value_if_error) - 如果第一个参数中的表达式导致错误，则返回 value_if_error。否则，返回表达式本身的值。

IFERROR (value, value_if_error) − Returns value_if_error if the expression in the first argument results in an error. Otherwise, returns the value of the expression itself.

表达式的返回值和 value_if_error 都必须具有相同的数据类型。否则，您将收到一条错误消息。

Both the return value of the expression and value_if_error must be of same data type. Otherwise, you will get an error.