DAX Optimisation Cumulative DISTINCTCOUNT

[krishanmsfabric]

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

[v-xiaotang · Accepted solution]

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.