Performance issue switch measure

[Ronald123]

Hello,

I have a measure in my report for counting the sum of the orderbook.

This measures must check two criteria. One for the days to count en the other te ratecode.

Below a part of the selection of this measure. 

Is it possible to write this measures better for a lower CPU?

Thanks,

Ronald

OrderbookDays= SUMX(ContItems;

                SWITCH(TRUE();

        [CountDays]=1 && ContItems[RATECODE]="STD";ContItems[RATE#1];

        [CountDays]=1 && ContItems[RATECODE]="FERR";ContItems[RATE#1];

        [CountDays]=1 && ContItems[RATECODE]="STD3";ContItems[RATE#1];

        [CountDays]=2 && ContItems[RATECODE]="STD";ContItems[RATE#2];

        [CountDays]=2 && ContItems[RATECODE]="FERR";ContItems[RATE#2];

        [CountDays]=2 && ContItems[RATECODE]="STD3";ContItems[RATE#2];

        [CountDays]=3 && ContItems[RATECODE]="STD";ContItems[RATE#3];

        [CountDays]=3 && ContItems[RATECODE]="FERR";ContItems[RATE#3];

        [CountDays]=3 && ContItems[RATECODE]="STD3";ContItems[RATE#3];

        [CountDays]=4 && ContItems[RATECODE]="STD";ContItems[RATE#4];

        [CountDays]=4 && ContItems[RATECODE]="STD3";ContItems[RATE#4])

[v-shex-msft · Accepted solution]

Hi @Ronald123,

I don't suggest you to use complex calculate formula as expression with sumx or other functions who contains loop features.(Obviously, it will affect performance)

For your scenario, you can try to use below measure if it work on your side with cost less system resource.

OrderbookDays =
SUMX (
    ContItems;
    IF (
        ContItems[RATECODE] IN { "STD"; "STD3"; "FERR" };
        SWITCH (
            [CountDays];
            1; ContItems[RATE#1];
            2; ContItems[RATE#2];
            3; ContItems[RATE#3];
            4; ContItems[RATE#4]
        )
    )
)

Optimizing DAX expressions involving multiple measures

Regards,

Xiaoxin Sheng