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

Optimize DISTINCTCOUNT in #dax with SQL Server 2012 SP1 CU 9 #ssas #tabular

If you use DISTINCTCOUNT measures in DAX, you know performance are usually great, but you might have also observed that the performance slow down when the resulting number is high (depending on other conditions, it starts decreasing between 1 and 2 million as a result).

If you have seen that, there is a good news. Microsoft fixed this issue (KB2927844) in SQL Server 2012 SP1 Cumulative Update 9. Performance improvement is amazing. With this fix, I have queries previously running in 15 seconds (cold cache) now running in less than 5 seconds. So if you have databases in Tabular with a column containing more than 1 million distinct values, probably it’s better you test this update. It’s available also for Power Pivot for Excel 2010, but not for Excel 2013 (as far as I know – Power Pivot for Excel 2013 updates are included in Excel updates). You can request the SP1CU9 here:

Please consider that the build of Analysis Services 2012 that fixes this issue is 11.0.3412 (so a following build should not require this hotfix – useful note for readers coming here in the future, when newer builds will be available).

UPDATE 2014-07-22: for the following major release, Analysis Services 2014, the fix has been released after RTM. You need the Build 12.00.2342 (or a more updated one), which is available in Cumulative Update 1 for RTM.

Published Monday, March 24, 2014 4:33 PM by Marco Russo (SQLBI)



Cos said:

well, it took them long enough -- CU9 !?  well, no worries, they'll have plenty opportunities to fix that fix, in later releases, ie, to break it  -- says the pessimist in me!  :-) JK -- congrats to Microsoft for coming up with the fix (sorely needed w/ big data) -- and for you guys, for staying on top of these issues and being the voice of those concerns and for always being on the bleeding edge of this technology!  kudos!!

March 25, 2014 4:34 AM

Cos said:

wait...!  how many things does that CU break already?  hmm... i wonder.... hope not too many... time for a good-ole-fashioned wash and rinse and rinse some check of all queries for baseline comparison !!!  :-)

March 25, 2014 4:37 AM

Deepika said:

IN POwer DAX till now its causing impact... Do we have any alternate?

January 10, 2018 12:55 AM

Deepika said:

i meant Power BI

January 10, 2018 12:56 AM

Marco Russo (SQLBI) said:

DISTINCTCOUNT might be slow if you have millions of unique values. If the number is smaller, then you might have issues caused by filter arguments in calculate or bidirectional filters.

January 12, 2018 1:40 AM
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