topic Re: Distinct Count Optimized when Filtered in DAX Commands and Tips

Distinct Count Optimized when Filtered

codyraptor — Tue, 23 May 2023 01:27:35 GMT

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.

Re: Distinct Count Optimized when Filtered

MattAllington — Tue, 23 May 2023 03:37:12 GMT

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.

Re: Distinct Count Optimized when Filtered

tamerj1 — Tue, 23 May 2023 05:08:27 GMT

Hi codyraptor
Please refer to

Analyzing the performance of DISTINCTCOUNT in DAX - SQLBI