Do DAX and MDX need a safe divide operator?

[Darren Gosbell]

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( = 0, NULL, / )**

DAX: IF( = 0, BLANK(), / )

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/SQLSer...-zero-operator

And Marco just added one for DAX here:

https://connect.microsoft.com/SQLSer...-zero-operator

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




Get More from the original blog...