Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

DAX measure optimization

Hello everyone,

 

I have read several posts and some articles about the optimization of a Dax measure but I am having some issue to put these advices into fruition. I am trying to had a measure flagging Inventory Items (direct SSAS connection) into a table. Here is the measure i campe up with: 

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

 

 

Having a large amount of item, it takes ages to show the values. Could you help me with some tips on how I could enhance my measure formula?

 

Best regards,

 

Marc 

Need Help
Tips and Tricks
  • MartynRamsden's avatar
    MartynRamsden
    6 years ago

    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.

     

  • Anonymous's avatar
    Anonymous
    6 years ago

    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 

     

     

     

6 Replies

  • MartynRamsden's avatar
    MartynRamsden
    Solution Sage
    6 years ago

    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.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Yes it helps, thank you very much.

       

      Marc 

      • dawnc63's avatar
        dawnc63
        Helper I
        6 years ago

        I am having a similar problem, and I've read much of the posts here on optimization, but I must be missing something.
        The only big fact table I have is a Date Table - everything else is based on measures and dates.  It was working just fine until I created this measure:

         

        Reserve requirement =
         
        VAR ReserveTable =
             SUMMARIZE( Dates,Dates[Year], "Reserve total", [Reserve requirement calc])

        RETURN
             IF (HASONEVALUE(Dates[Year]), [Reserve requirement calc], SUMX(ReserveTable,[Reserve requirement calc])
             )

        The [Reserve requirement calc] is based on more measures:
        Reserve requirement calc =
          IF (
               [Contract Total increment] - [Tax revenue] > 0,
               [Contract Total increment] - [Tax revenue] ,
               BLANK()
         )


        which was working fine until I added the [Reserve requirement] measure that provides the total.

         

        Is there a way of optimizing this?  It is taking about 2 minutes to load the card visual and almost 3 minutes for the table visual.

         

        Any tips would be so appreciated.  I'm finally catching on, but now that other people can see what is possible, I'm going to get more and more complicated problems to solve! 

         

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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