Forum Discussion

Srikantht's avatar
Srikantht
Icon for Helper II rankHelper II
3 years ago

Optimize my dax query

Hi,

 

Can anyone optimize the below query. it's taking 15 seconds to load when I run in Dax studio. and this measure I am using for conditional formatting. 

CAGR = Var matrix1 =  CAGR (% TY v LLY)]
Var matrix2 =
    CALCULATE(CAGR (% TY v LLY)],
    ALL('STATES')
    , ALL('STORES')
, ALL('PRODUCT')
    )
Return
IF(matrix1 > matrix2, "GREEN", "RED")

 

Thanks in advance. 

 

 

Need Help

5 Replies

  • Ahmedx's avatar
    Ahmedx
    Icon for Super User rankSuper User
    3 years ago
    CAGR = Var matrix1 =  CAGR (% TY v LLY)]
Var matrix2 =
    CALCULATE(CAGR (% TY v LLY)],
REMOVEFILTERS()
    )
Return
SWITCH( TRUE,matrix1 > matrix2, "GREEN", "RED")
    • Srikantht's avatar
      Srikantht
      Icon for Helper II rankHelper II
      3 years ago

      Hi Ahmedx 

       

      Thank you for your quick response.

       

      In the above you dax why you did not consider three tables of states, stores and product. ?

      will removefilters appply to 3 tables whitout metioning in formaula. ?

       

      Thanks

  • Ahmedx's avatar
    Ahmedx
    Icon for Super User rankSuper User
    3 years ago

    Didn't understand! has your question been optimized or not?

    • Srikantht's avatar
      Srikantht
      Icon for Helper II rankHelper II
      3 years ago

      Ahmedx 

       

      your measure not worked it gives "RED" for every category. 

       

      my question in your measure, why you did not take three tables? 

    • Srikantht's avatar
      Srikantht
      Icon for Helper II rankHelper II
      3 years ago

      Hi 

       

      Anyone have idea to optimize this measure?

       

      and also I 've created this measure in report level. and data is coming from SSAS model. if i create same measue in ssas model and deploy then add to report. will it be optimse? becuase this measure comes from then SSAS model. 

       

       

      anyone help me in this?

       

      Thanks.