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.