Do DAX and MDX need a safe divide operator?

I’ve been teaching an MDX course for the last few days as well as reading Marco and Alberto’s excellent PowerPivot book on the train and it struck me that every time I do a division in both languages I seem to be using the following pattern in order to avoid returning an error to the user

MDX: IIF( <denominator> = 0, NULL, <numerator> / <denominator> )  

DAX: IF( <denominator> = 0, BLANK(), <numerator> / <denominator> )

I know that languages like C++ and C# don’t test for this automatically as it’s an extra operation that is not always required. So I've sort of been pre-conditioned into thinking this is normal. But particularly in DAX, which is supposed to be as simple and user friendly as possible, I was thinking that the divide operator should do this automatically or we should have something like a SafeDivide() function or maybe a different operator.

If you want the calculation to behave differently when the denominator is 0 or blank then you could still specifically test for that, but at the moment I can’t think of a compelling reason where I would want to show the user a “divide by 0” error.

Chris Webb has had a suggestion up on connect for a while to have this feature added to MDX here:

https://connect.microsoft.com/SQLServer/feedback/details/448127/mdx-needs-a-special-division-by-zero-operator

And Marco just added one for DAX here:

https://connect.microsoft.com/SQLServer/feedback/details/622995/dax-needs-a-special-division-by-zero-operator

Go and vote if you think this would be a good thing to have.

Print | posted on Friday, November 19, 2010 8:05 AM

Comments on this post

# getting the data currency from the OLAP

Requesting Gravatar...
Hi Darren,

Could you please let me know how to load output of the mdx query into sql table. The output of the mdx query should be the last nonempty max(date) for any measure.

regards.....Preethi
Left by Preethi Khatore on Jan 04, 2011 3:52 PM

# re: Do DAX and MDX need a safe divide operator?

Requesting Gravatar...
SafeDivide function would be great. Couldn't come up with any situations where it would be a hindrance.
Left by Claire on Feb 01, 2011 8:02 AM

# re: Do DAX and MDX need a safe divide operator?

Requesting Gravatar...
Thanks. It's usefull
Left by bloger7791 on Apr 03, 2013 4:43 PM

Your comment:

 (will show your gravatar)