DAX Optimisation Cumulative DISTINCTCOUNT

Question

Hi All,&nbsp;I am wondering if someone can help me with a very slow DAX calculation.&nbsp;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.&nbsp;Our financial year lasts from 1st of August and ends 31st of July.&nbsp;Product ID's are grouped under a Grouping ID.&nbsp;&nbsp;&nbsp;DAXI 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 (&nbsp;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)&nbsp;Active Customer Count = CALCULATE(&nbsp; &nbsp; DISTINCTCOUNT('Customer Tbl'[Customer ID]),&nbsp; &nbsp; FILTER(&nbsp; &nbsp; &nbsp; &nbsp; ALL('Customer Tbl'[Fiscal Year || Grouping ID || Customer ID]),&nbsp; &nbsp; &nbsp; &nbsp; [Transaction Amnt]&gt;0))&nbsp;Cumulative Active Customer Count = CALCULATE(&nbsp; &nbsp; [Active Customer Count],&nbsp; &nbsp; CALCULATETABLE(&nbsp; &nbsp; &nbsp; &nbsp; DATESYTD('Dim Date'[Date], "31-07"),&nbsp; &nbsp; &nbsp; &nbsp; 'Dim Date'[Is Future Date] = "Not Future Date"&nbsp; &nbsp; ))&nbsp;Any idea on what I can do to speed up this cumulative count?&nbsp;Link to file:https://drive.google.com/file/d/18OuoPSx2pFmk0Q-NCU1hhPcFyt_pQvr3/view?usp=sharing

v-xiaotang · Accepted Answer

Hi&nbsp;krishanmsfabric&nbsp;
Thanks for reaching out to us.
please refer to
(1)&nbsp;FILTER vs CALCULATETABLE: optimization using cardinality estimation - SQLBI
"&nbsp;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."
&nbsp;
(2)&nbsp;Why my Power BI Matrix or Table Visual is SLOW - RADACAD

&nbsp;
Best Regards,
Community Support Team _Tang
If this post&nbsp;helps, please consider&nbsp;Accept it as the solution&nbsp;to help the other members find it more quickly.

Forum Discussion

DAX Optimisation Cumulative DISTINCTCOUNT

1 Reply

Related content

Fabric - Copilot optimisation

Validation/optimisation options for AI features

Cumulative Total

Creating Cumulative Graphs

DISTINCTCOUNT with filters