Poor performance in dax

[ShenWick]

I have created this dax, which leads to poor performance in the report

CALCULATE( [Total Overdue Balance],
FILTER( Distinct(FACT_CUSTOMER_INVOICE,
COUNTROWS(
FILTER('Age Groups',
[Over Due Days]>='Age Groups'[Start Day] &&
[Over Due Days]<='Age Groups'[End Day]))>0))

Is there an alternative dax to optimize the performance

[Sahir_Maharaj]

One possible optimization could be to use the SUMX function instead of the CALCULATE and FILTER functions:

SUMX(
FILTER(
'FACT_CUSTOMER_INVOICE',
COUNTROWS(
FILTER(
'Age Groups',
[Over Due Days] >= 'Age Groups'[Start Day] &&
[Over Due Days] <= 'Age Groups'[End Day]
)
) > 0
),
[Total Overdue Balance]
)

Another possible optimization could be to precalculate the result of the inner COUNTROWS function using a calculated column in the 'FACT_CUSTOMER_INVOICE' table, and then use that column in the DAX formula. This can reduce the computational overhead of the DAX formula at runtime.

Overdue Age Group Count =
COUNTROWS(
FILTER(
'Age Groups',
[Over Due Days] >= 'Age Groups'[Start Day] &&
[Over Due Days] <= 'Age Groups'[End Day]
)
)

SUMX(
FILTER(
'FACT_CUSTOMER_INVOICE',
'FACT_CUSTOMER_INVOICE'[Overdue Age Group Count] > 0
),
[Total Overdue Balance]
)

Let me know if you might need further assistance.

[ShenWick]

Hi @Sahir_Maharaj 
Your suggestions helped me to improve the performance, is there anything else that I can do further improve the performance?