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: http://support.microsoft.com/kb/2931078.
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.
Counts the number of distinct values in a column.
DISTINCTCOUNT ( <ColumnName> )