Optimizing DAX

[Anonymous]

Dear Community,

I have the below DAX that it is slow : couldn't load the data for this visual - out of memory

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 =

    CALCULATE(

        MAXX(

            FILTER(

                SUMMARIZE(

                    SITE,

                    SITE[SRU_ID],

                    SITE[SRU_DESCL_EN],

                    "DaysPaymentTerms", [Average Weight Theoretical]

                ),

                [DaysPaymentTerms] = MaxDaysPaymentTerms

            ),

            SITE[SRU_DESCL_EN]

        ),

        ALL(Suppliers[Supplier (ERP)]), ALL(SITE)

    )

VAR _topNSRU =

    TOPN(

        1,

        SUMMARIZE(

            SITE,

            SITE[SRU_ID],

            SITE[SRU_DESCL_EN],

            "TotalSpend", SUM(V_RPT_F_PAYTERM[AMOUNT_HT])

        ),

        [TotalSpend], DESC

    )

VAR _highestSpendSRU =

    IF(

        COUNTROWS(_topNSRU) > 0,

        SELECTCOLUMNS(_topNSRU, "SRU_DESCL_EN", SITE[SRU_DESCL_EN]),

        BLANK()

    )

RETURN

    IF(ISBLANK(_sru) && NOT(ISBLANK([Spend])), _highestSpendSRU, _sru)

[audreygerred]

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]

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

[audreygerred]

Unfortunately, using ALL will cause performance decline.