Distinct Count Optimized when Filtered

[codyraptor]

I have a simple DAX query counting distinct ID's in a fact table.  When I run a simple 'Distinctcount(ID)....I get the answer in 7ms.  However...I need to run a distinct count on ID's where fact[column] <> 'XX'.  My calculation jumps to 700ms once a filter is applied.  I've tried applying the filter on the fact table as well as on the DIM table (1 to many)....but can't seem to optimize and get the initial speed.  Any thoughts?  I've read the sqlbi paper on distinct count...but I don't see anything specific to applying filters in the calculation.

[tamerj1 · Accepted solution]

Hi @codyraptor 
Please refer to 

Analyzing the performance of DISTINCTCOUNT in DAX - SQLBI

[MattAllington]

My guess is that the first result is sourcing the answer from metadata. Once you apply a filter, it has to do the actual count. It's only a guess.