There are as many ways of writing a DAX measure as there are BI developers out there, but here is my way of maintaining high performance when counting distinct values in a (100M+) tabular model.
Normally this is how you typically write a DistinctCount() measure:
No. of Customers = DISTINCTCOUNT(’Fact'[CustomerID])
But as rows keep appearing in the model, you might experience a decrease in performance since the query engine now needs to go through more rows and sort more values. And instead of writing a DAX measure that can take you to the moon, this problem can be solved with some small architectural changes in the model.
First, I’m creating a new help table, consisting of two columns, the key is a distinct of IDs that I initially wanted to do a count of. Then I’m adding a column containing 1 for each ID. This way I can now do a SUM() of values in my new column instead of counting values, and with the same result but with a much faster query. All you have to do now is to create a relationship over the ID columns with a both way cross filter direction so that the fact table filters your new help table.
And of course, change your existing measure to this: No. of Customers (Optimized) = SUM(’HelpTable_DistinctCount'[No_of_Customers])
This works like a charm, especially when analyzing over a dimension with many values, i. e. a Date dimension.
Create New Help Table step by step
HelpTable_DistinctCount = DISTINCT(’Fact'[CustomerID])
No_of_Customers = 1