THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

SQLBI - Marco Russo

Please visit the same blog at - comments are now disabled here but you can comment on the new blog (all blog posts available here are available there, too).
You can follow me on Twitter: @marcorus

DIVIDE vs division operator in #dax

Alberto Ferrari wrote an interesting article about DIVIDE performance in DAX. This new function has been introduced in SQL Server Analysis Services 2012 SP1, so it is available also in Excel 2013 (which still doesn’t have other features/fixes introduced by following Cumulative Updates…). The idea that instead of writing:

IF ( Sales[Quantity] <> 0, Sales[Amount] / Sales[Quantity], BLANK () )

you can write:

DIVIDE ( Sales[Amount], Sales[Quantity] )

There is a third optional argument in DIVIDE that defines the result in case the denominator (second argument) is zero, and by default its value is BLANK, so I omitted the third argument in my example.

Using DIVIDE is very important, especially when you use a measure in MDX (for example in an Excel PivotTable) because it raise the chance that the non empty evaluation for the result is evaluated in bulk mode instead of cell-by-cell. However, from a DAX point of view, you might find it’s better to use the standard division operator removing the IF statement. I suggest you to read Alberto’s article, because you will find that an expression applying a filter using FILTER is faster than using CALCULATE, which is against any rule of thumb you might have read until now!

Again, this is not always true, and depends on many conditions – trying to simplify, we might say that for a simple calculation, the query plan generated by FILTER could be more efficient – but, as usual, it depends, and 90% of the times using FILTER instead of CALCULATE produces slower performance. Do not take anything for granted, and always check the query plan when performance are your first issue!

Published Thursday, July 24, 2014 8:22 AM by Marco Russo (SQLBI)



Meenu said:

Hi Marco,

I have two measures in my tabular model 'ProductsPurchasedbyUser' and 'ProductsinUse'and getting %ofUsage.It's working fine in excel pivot table with tabular connection but

when I load these two measures,usernames,productnames into powerpivot and tried creating %calc.field within the powerpivot.

I am getting 200% instead of average of two 100% cells(user with 2 product names,if I remove product from table then its summing the %).Without changing value field settings to Average, is there any other way to get the correct value?

I also tried  powerpivot setting "summarized by" , it didn't work.

Thanks in advance...

March 1, 2015 11:30 AM

Marco Russo (SQLBI) said:

Meenu, it's not clear what you are doing - probably you are confusing measures in Power Pivot with features built in in pivot table in Excel (such as % of...) but I am not sure about what you are doing based on your description.

March 14, 2015 4:33 PM
New Comments to this post are disabled

About Marco Russo (SQLBI)

Marco Russo is a consultant, writer and trainer specialized in Business Intelligence with Microsoft technologies. He runs the SQLBI.COM website, which is dedicated to distribute resources useful for BI developers, like Integration Services components, Analysis Services models, tools, technical information and so on. Marco is certified as MCT, MCDBA, MCSD.NET, MCSA, MCSE+I.

This Blog



Privacy Statement