DAX:适当使用错误函数DAX: Appropriate use of error functions

作为数据建模者,编写可能引发评估时间错误的 DAX 表达式时,可以考虑使用两个有用的 DAX 函数。As a data modeler, when you write a DAX expression that might raise an evaluation-time error, you can consider using two helpful DAX functions.

  • ISERROR 函数,它采用单个表达式并在该表达式导致错误时返回 TRUE。The ISERROR function, which takes a single expression and returns TRUE if that expression results in error.
  • IFERROR 函数,它采用两个表达式。The IFERROR function, which takes two expressions. 如果第一个表达式导致错误,则返回第二个表达式的值。Should the first expression result in error, the value for the second expression is returned. 事实上,这样可以使将 ISERROR 函数嵌套在 IF 函数内的实现更为优化。It is in fact a more optimized implementation of nesting the ISERROR function inside an IF function.

但是,尽管这些函数可能会很有帮助,并有助于编写易于理解的表达式,但它们也会显著降低计算的性能。However, while these functions can be helpful and can contribute to writing easy-to-understand expressions, they can also significantly degrade the performance of calculations. 出现这种情况是因为这些函数会增加所需的存储引擎扫描次数。It can happen because these functions increase the number of storage engine scans required.

大多数评估时间错误是由于意外的空白或零值或无效的数据类型转换而导致的。Most evaluation-time errors are due to unexpected BLANKs or zero values, or invalid data type conversion.

建议Recommendations

最好避免使用 ISERROR 和 IFERROR 函数。It's better to avoid using the ISERROR and IFERROR functions. 而应在开发模型和编写表达式时应用防御策略。Instead, apply defensive strategies when developing the model and writing expressions. 策略可以包括:Strategies can include:

  • 确保将质量数据加载到模型中: 使用 Power Query 转换可删除或替换无效或缺失的值,以及设置正确的数据类型。Ensuring quality data is loaded into the model: Use Power Query transformations to remove or substitute invalid or missing values, and to set correct data types. 出现错误(如数据转换无效)时,Power Query 转换还可用于筛选行。A Power Query transformation can also be used to filter rows when errors, like invalid data conversion, occur.

    还可以通过将模型列“可以为 Null” 属性设置为“关闭”来控制数据质量,这将导致在遇到空白时无法进行数据刷新。Data quality can also be controlled by setting the model column Is Nullable property to Off, which will fail the data refresh should BLANKs be encountered. 如果出现此错误,则成功刷新后加载的数据将保留在表中。If this failure occurs, data loaded as a result of a successful refresh will remain in the tables.

  • 使用 IF 函数: IF 函数逻辑测试表达式可确定是否会出现错误结果。Using the IF function: The IF function logical test expression can determine whether an error result would occur. 请注意,与 ISERROR 和 IFERROR 函数一样,此函数可能会导致额外的存储引擎扫描,但其性能可能更高,因为不会引发错误。Note, like the ISERROR and IFERROR functions, this function can result in additional storage engine scans, but will likely perform better than them as no error needs to be raised.

  • 使用容错函数: 某些 DAX 函数将测试并弥补错误情况。Using error-tolerant functions: Some DAX functions will test and compensate for error conditions. 这些函数允许你输入将返回的替代结果。These functions allow you to enter an alternate result that would be returned instead. DIVIDE 函数是其中一个示例。The DIVIDE function is one such example. 有关此函数的其他指导,请阅读 DAX:DIVIDE 函数与除法运算符 (/) 一文。For additional guidance about this function, read the DAX: DIVIDE function vs divide operator (/) article.

示例Example

以下度量值表达式测试是否会引发错误。The following measure expression tests whether an error would be raised. 将在此实例中返回空白(如果不提供具有 value-if-false 表达式的 IF 函数)。It returns BLANK in this instance (which is the case when you do not provide the IF function with a value-if-false expression).

Profit Margin
= IF(ISERROR([Profit] / [Sales]))

该度量值表达式的下一版本已通过使用 IFERROR 函数代替 IF 和 ISERROR 函数得以改进。This next version of the measure expression has been improved by using the IFERROR function in place of the IF and ISERROR functions.

Profit Margin
= IFERROR([Profit] / [Sales], BLANK())

但是,该度量值表达式的最终版本得出相同的结果,不过更高效更顺畅。However, this final version of the measure expression achieves the same outcome, yet more efficiently and elegantly.

Profit Margin
= DIVIDE([Profit], [Sales])

后续步骤Next steps

有关本文的详细信息,请参阅以下资源:For more information about this article, check out the following resources: