Power Pivot Principles: The A to Z of DAX Functions – IFERROR

In our long-established Power Pivot Principles articles, we continue our series on the A to Z of Data Analysis eXpression (DAX) functions.  This week, we look at IFERROR. 

The IFERROR function

The IFERROR function is one of the logical functions.  It is a valuable tool for handling errors in your expressions by allowing you to specify a backup value in case the main calculation results in an error.  It employs the following syntax:

This function has two [2] parameters:

• value: this is required and represents the expression you want to evaluate
• value_if_error: the value to return if value results in an error.  This can be any valid DAX expression or a literal value.

It has the following remarks:

• both value and value_if_error must be of the same data type
• if either value or value_if_error is an empty cell, it is treated as an empty string ("")
• an equivalent expression for IFERROR function is IF ISERROR

• this function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.

Let’s consider the following example where we have the tbl_sales Table:

We will write the following DAX to check how much the sales increased or decreased compared to the previous year, and if an error occurs, it should return a value of zero [0]:

In the DAX statement, we construct the following:

• the first argument is the sum of the column Sales_CY divided by the sum of the column Sales_PY
• the second and last argument is  the retuning value in case the expression above occurred an error.

Then we can out this measure on to the PivotTable on to check the result:

Since we do not have sales for the previous year to the products “A”, “D”,”F” and “J”, the IFERROR function returns zero [0] as we cannot divide any number by zero [0]. 

Best Practice suggestions:

• use IFERROR wisely, as excessive error handling can impact performance
• consider alternative functions like DIVIDE with a built-in error handling mechanism
• for complex error handling scenarios, explore nested IFERROR functions or other DAX constructs.

Come back next week for our next post on Power Pivot in the Blog section.  In the meantime, please remember we have training in Power Pivot which you can find out more about here.  If you wish to catch up on past articles in the meantime, you can find all of our Past Power Pivot blogs here.