Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Optimize Dax measure

I have a complex model and the measures created from this model uses USERELATIONSHIP when calculate measure. Hence, I would like to optimize the dax expression.

 

 

[Margin (Current)] = 

Var MaxDate = calculate (max ( FACTProduct[Date]) , ALL(FACTProduct) , CROSSFILTER(DIMProductHierarchy[Scan Product], FACTProduct[Scan_Product], NONE))

RETURN

calculate ( 
sum (FACTSales[Sales]) - sum (FACTProductWaste[Marks]) + ( sum (FACTProduct[Amt]) - sum (FACTProduct[CancelSales]) ) - sum (FACTSales[Cost]) 
, USERELATIONSHIP( Date[Date] , 'DateProduct'[Date] )
, USERELATIONSHIP(DateProduct[Concat],FACTSales[Concat] )
, USERELATIONSHIP(Product[Concat],FACTProductWaste[Concat] )
, USERELATIONSHIP(DateProduct[Concat],FACTProduct[Concat] )
, USERELATIONSHIP(DIMPromo[ID],DateProduct[ID])
, 'DatePromo'[Date] <= MaxDate )

 

 

I using DAX studio to time to run the above Dax, it took 2427ms as shown below: 

How can I optimize the dax

Need Help
Show and Tell
Tips and Tricks

1 Reply

  • v-janeyg-msft's avatar
    v-janeyg-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hello, Anonymous 

     

    The code basically has nothing to optimize. Since measure will automatically change in the visual according to the context, it's not sure whether some conditions in your code are not necessary and can be deleted.

     

    Did I answer your question ? Please mark my reply as solution. Thank you very much.
    If not, please upload some insensitive data samples and expected output.


    Best Regards,

    Community Support Team _ Janey