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