DAX Optimisation Cumulative DISTINCTCOUNT

krishanmsfabric — Wed, 09 Nov 2022 11:07:20 GMT

Hi All,

I am wondering if someone can help me with a very slow DAX calculation.

Business Case:
We consider a customer to be financially active on our system for a financial year if the sum of their transactions for a financial year for any Group ID is greater than 0.

Our financial year lasts from 1st of August and ends 31st of July.

Product ID's are grouped under a Grouping ID.

DAX

I created a grouping calculated column in the customer table, (Fiscal Year || Grouping ID || Customer ID) { to avoid having to create joins in the query step).

I want to calculate the number of financially active customers ( Active Customer Count ) and a cumulative count of this metric (Cumulative Active Customer Count). However the cumulative sum is very slow (20-30 seconds long)

Active Customer Count =

CALCULATE(

DISTINCTCOUNT('Customer Tbl'[Customer ID]),

FILTER(

ALL('Customer Tbl'[Fiscal Year || Grouping ID || Customer ID]),

[Transaction Amnt]>0))

Cumulative Active Customer Count =

CALCULATE(

[Active Customer Count],

CALCULATETABLE(

DATESYTD('Dim Date'[Date], "31-07"),

'Dim Date'[Is Future Date] = "Not Future Date"

)

Any idea on what I can do to speed up this cumulative count?

Link to file:

https://drive.google.com/file/d/18OuoPSx2pFmk0Q-NCU1hhPcFyt_pQvr3/view?usp=sharing

Re: DAX Optimisation Cumulative DISTINCTCOUNT

v-xiaotang — Wed, 09 Nov 2022 09:59:38 GMT

Hi krishanmsfabric

Thanks for reaching out to us.

please refer to

(1) FILTER vs CALCULATETABLE: optimization using cardinality estimation - SQLBI

" It is correct to assume that separate filters are better, but if you have many filters and a distinct count calculation, you might try to consolidate filters in order to improve performance. I expect this behavior to be improved in future releases of Power Pivot and Analysis Services Tabular."

(2) Why my Power BI Matrix or Table Visual is SLOW - RADACAD

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

topic Re: DAX Optimisation Cumulative DISTINCTCOUNT in DAX Commands and Tips

DAX Optimisation Cumulative DISTINCTCOUNT

Re: DAX Optimisation Cumulative DISTINCTCOUNT