Using IFERROR/ISERROR in your DAX formula

Thanks to all the folks who I talked to at TechEd NA who gave me encouraging words to do more blog posts. I will try to add more blog posts in the near future. Today a small nugget / tip.

Many DAX measure will use IFERROR/ISERROR in the measure to catch a divide by zero error. E.g. it can look like this:

Measure test := IFERROR([Measure 1] / [Measure 2], 0, [Measure 1] / [Measure 2])

Or in a calculate column:

=IFERROR(SEARCH(“Product03?1*”,DimProduct[StyleName]),-1)

Although on the surface this seems very innocent we have discovered that using IFERROR / ISERROR can cause significant performance issues. So don’t use this function unless you really need to.

In SQL Server 2012 we made using the IFERROR statement obsolete when using SEARCH (from this post):

In SQL Server 2012 the Search function has an extra parameter that let’s you return a value if nothing is found. In 2008 R2 if no value is found the function will throw an error, which makes this function not easy to work with.

Now you can just do this:

=SEARCH(“Dollar”,Currency[CurrencyName],1,-1)

It will return -1 if no string is found that matched “Dollar”.

 

To have the divide by zero error not show up we recommend the following:

Measure test :=IF([Measure 2] <> 0,  [Measure 1] / [Measure 2], 0)

  • Colin Banfield

    Kasper,

    It’s good to see you posting again. There will always be situations (like in any other programming language) where testing for errors cannot be avoided. For large data sets, we are now in the awkward position of having no acceptable error checking functions.

    My hope is that the AS team takes error checking seriously enough that they will be motivated to optimize the functions, or rewrite them from scratch. The current un-optimized algorithms were no doubt inherited from Excel, where the largest dataset won’t have more than 1M rows (and far less in most cases).

  • What is your feeling on using the DIVIDE function?