Optimizing DAX

Question

Dear Community,&nbsp;I have the below DAX that it is slow : couldn't load the data for this visual - out of memory&nbsp;The goal for the dax is to show the SRU_DESCL_EN for the Supplier ERP having the highest best payment terms and if a suppliers ERP ( that's why I put the condition in the return) if it returns blank since an ERP could have many SRU then return the one with highest spend:Measure =VAR MaxDaysPaymentTerms = CALCULATE([Best Payment Terms in SG (# Days)], ALL(Suppliers[Supplier (ERP)]))VAR _sru = &nbsp; &nbsp; CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; MAXX(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FILTER(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SUMMARIZE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SITE,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SITE[SRU_ID],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SITE[SRU_DESCL_EN],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "DaysPaymentTerms", [Average Weight Theoretical]&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [DaysPaymentTerms] = MaxDaysPaymentTerms&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SITE[SRU_DESCL_EN]&nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; &nbsp; &nbsp; ALL(Suppliers[Supplier (ERP)]), ALL(SITE)&nbsp; &nbsp; )VAR _topNSRU = &nbsp; &nbsp; TOPN(&nbsp; &nbsp; &nbsp; &nbsp; 1,&nbsp; &nbsp; &nbsp; &nbsp; SUMMARIZE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SITE,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SITE[SRU_ID],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SITE[SRU_DESCL_EN],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "TotalSpend", SUM(V_RPT_F_PAYTERM[AMOUNT_HT])&nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; &nbsp; &nbsp; [TotalSpend], DESC&nbsp; &nbsp; )VAR _highestSpendSRU = &nbsp; &nbsp; IF(&nbsp; &nbsp; &nbsp; &nbsp; COUNTROWS(_topNSRU) &gt; 0,&nbsp; &nbsp; &nbsp; &nbsp; SELECTCOLUMNS(_topNSRU, "SRU_DESCL_EN", SITE[SRU_DESCL_EN]),&nbsp; &nbsp; &nbsp; &nbsp; BLANK()&nbsp; &nbsp; )RETURN&nbsp; &nbsp; IF(ISBLANK(_sru) &amp;&amp; NOT(ISBLANK([Spend])), _highestSpendSRU, _sru)

audreygerred · Answer

Unfortunately, using ALL will cause performance decline.

audreygerred · Answer

Hi! It is likely the ALL function taht you are using with CALCULATE. Do you need the measure to ignore page, report, and visual level filtering? If you do not need this measure to ignore any filtering you or the user applied to the report, then ALL is not needed and you can improve the performance by removing.

anonymous · Answer

Hello&nbsp;audreygerred&nbsp;, thank you for your reply, yes All function is to remove effect of external slicers/ filters

Forum Discussion

Optimizing DAX

3 Replies

Related content

DAX performance optimization

Dax code optimization

DAX query with SUMMARIZECOLUMNS - Optimization

DAX optimization problem

DAX Measure Optimization - Please help