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.


Feedback

# getting the data currency from the OLAP

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 1/4/2011 3:52 PM | Preethi Khatore

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

SafeDivide function would be great. Couldn't come up with any situations where it would be a hindrance. 2/1/2011 8:02 AM | Claire

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

Thanks. It's usefull 4/3/2013 4:43 PM | bloger7791

Post a comment





 

 

News

About Me
I am a consultant, based in Melbourne Australia. I primarily work in the Business Intelligence area with SQL Server, although I also dabble in Content Management Server and .Net coding

Contact Me

MVP


Legal
Any and all code, software, examples, suggestions and anything else on this web site is available for you to use at your own risk. No warranty is expressed or implied.
Views and Opinions
The views and opinions expressed on this web site are not necessarily the views or opinions of my employer.

Subscribe in Bloglines Subscribe in NewsGator Online

Locations of visitors to this page

Twitter












Tag Cloud


Article Categories

Archives

Post Categories

.Net Blogs

Aussie Bloggers

BI Blogs

CMS Blogs

Syndication: