Slow calculations with DISTINCTCOUNT

[aviritia]

Hello Guys!

I have a list of users with all their accesses and I wanted to see how many users I have for the same country, same system, same risk ID and same business role. To achieve that I used DISTINCTCOUNT function:

=IF([No. of roles per user] = 1; 
CALCULATE(DISTINCTCOUNT([User ID]);
FILTER('raw data';[Country] = EARLIER([Country]));
FILTER('raw data';[System] = EARLIER([System]));
FILTER('raw data';[Access Risk ID] = EARLIER([Access Risk ID]));
FILTER('raw data';[Composite/Business Role] = EARLIER([Composite/Business Role])));1)

And it works perfectly, the outcome is correct, however I have a huge amount of data and DISTINCTCOUNT slows down my calculations a lot. 

Do you maybe know or have an idea how can I replace DISTINCTCOUNT funtion to get the identical result but faster? Each quarter I will have more and more data (now it's around 300k lines, so next quarter it will be twice that) and it will be getting only worse with the way I did that. 

I'd appreciate your help.

Best regards

Kate

[Anonymous]

I ran into the similar issue while doing it in calculated column. Can you post the formula that you modified to make it work.

Appreciate any help!

[akyler]

 In general, I think DISTINCTCOUNT causes performance issues.  COUNTROWS on a summarized table might actually provide much better performance.  Something like:

=COUNTROWS(
  SUMMARIZE(
    FILTER('raw data',
      [Country] = EARLIER([Country])
      &&[System] = EARLIER([System])
      &&[Access Risk ID] = EARLIER([Access Risk ID])
      &&[Composite/Business Role] = EARLIER([Composite/Business Role]))
   ,[User ID]))

[Anonymous · Accepted solution]

@aviritia,

This article gives a perfect explaination on why removing FILTER will speed up your DAX.