Edit

Appropriate use of error functions

  • Article

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.

  • The ISERROR function, which takes a single expression and returns TRUE if that expression results in error.
  • The IFERROR function, which takes two expressions. Should the first expression result in error, the value for the second expression is returned. 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

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:

  • 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. A Power Query transformation can also be used to filter rows when errors, like invalid data conversion, occur.

    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.

  • Using the IF function: The IF function logical test expression can determine whether an error result would occur. 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.

  • 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. The DIVIDE function is one such example. 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. 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]))

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])

Related content