Events
Mar 31, 11 PM - Apr 2, 11 PM
The ultimate Microsoft Fabric, Power BI, SQL, and AI community-led event. March 31 to April 2, 2025.
Register todayThis browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
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.
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.
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.
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])
Events
Mar 31, 11 PM - Apr 2, 11 PM
The ultimate Microsoft Fabric, Power BI, SQL, and AI community-led event. March 31 to April 2, 2025.
Register todayTraining
Learning path
Use DAX in Power BI Desktop - Training
This learning path introduces Data Analysis Expressions (DAX) and provides you with foundational skills required to enhance semantic models with calculations. It starts by describing Power BI Desktop model structure and how it can be enhanced with DAX calculations. It then describes how you can write DAX formulas and the different types of model calculations, including calculated tables and columns, and measures. Evaluation contexts are introduced, and subsequent lessons describe how to write DAX formulas t