DAX measure optimization

Hi Anonymous 

It's worth using variables for any expression which is computed multiple times, that way, they're only calculated once.

I'd also reccommend using the SWITCH function in place of nested IF statements. It doesn't improve performance but is easier to read.

See if this helps at all:

Cluster =
VAR SumDSI = SUM ( INVENTORY[DSI] )
VAR Result =
    SWITCH (
        TRUE (),
        SUM ( INVENTORY[Annual_Usage_(Current_Year)] ) = 0, "Obsolete",
        DATEDIFF ( SUM ( 'LAST INVOICE DATE'[Date] ), TODAY (), DAY ) >= 270, "Obsolete Warning",
        SumDSI >= 361
            && SumDSI <= 720, "Excess Warning",
        SumDSI >= 721, "Excess",
        "Working"
    )
RETURN Result

Best regards,
Martyn

If I answered your question, please help others by accepting it as a solution.

Im currenly having an issue with VAR data. When i try to run this with all that dax measurements i run out of memory. As you can see i only have 7 measurments in this because i couldnt figure out which measurement was causing me the issue. Unfortunately, its this one. Does anyone know of a diffrent way of attiving this? basically i want to subtract the previous dimension with the current dimension. these are based on time stamps. so in excell it would be =ABS ( A2-A3). Something like that but because power bi is not set up like this i had to make another equation basically to get the dates in order. Which is the first picture.

Any help would be appreciated