Forum Discussion

benZert's avatar
benZert
New Member
3 years ago

Trying to optimize a measure

Hi,

 

I've been tasked with 'speeding up' this DAX measure but I'm not sure where to start, does anybody have any suggestions on a way to make this measure work faster obviously still get the same result? Apologies if in the wrong forum

 

 

 

AvgStockOfDays2 = 
var avgcnt = max('Product'[CountOfDaysFwd])
     VAR FirstDayMAT = MAX ( PlanDueDate[PlanDueDate_DateSeqNumber])+1
     
      VAR LastDayMAT = FirstDayMAT + (avgcnt -1)
      
      Var StockOH = 
        CALCULATE (
    SUM ( Inventory_Measures[m_QtyOnHand] ),
    LASTNONBLANK ( LoadDate[LoadDatePK], CALCULATE ( SUM (Inventory_Measures[m_QtyOnHand] ) ) )
)

    VAR Period4W =
        CALCULATETABLE (
            VALUES (PlanDueDate[PlanDueDatePK] ),  ALLEXCEPT (
                'PlanDueDate',
               
                'PlanDueDate'[PlanDueDate_DayOfWeek]
            ),

            'PlanDueDate'[PlanDueDate_DateSeqNumber] >= FirstDayMAT 
                && PlanDueDate[PlanDueDate_DateSeqNumber] <= LastDayMAT
            

              )
              

		   VAR AvgMrp =
             CALCULATE (
                AVERAGEX ( Period4W,IF(ISBLANK( [QtyMrpFull]),0,[QtyMrpFull])
                 ),
                    ALL(PlanDueDate)
            )
          
            Var result =    
           DIVIDE(StockOH,AvgMrp,0)
        
    RETURN
        Result

 

 

 

Thanks 

Need Help

1 Reply

  • v-jingzhang's avatar
    v-jingzhang
    Icon for Community Support rankCommunity Support
    3 years ago

    Hi benZert 

     

    Is the [QtyMrpFull] a measure? If so, what is its formula? In this section, it will be evaluated twice. So if you can evaluate it only once, it may improve the performance to some degree. 

    Try

    AVERAGEX (
    Period4W,
    VAR _qtyMrpFull = [QtyMrpFull]
    RETURN
        IF ( ISBLANK ( _qtyMrpFull ), 0, _qtyMrpFull )
)

     

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it.