Forum Discussion

Ronald123's avatar
Ronald123
Resolver III
8 years ago
Solved

Performance issue switch measure

 

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])

Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    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

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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