Optimize my dax query

[Srikantht]

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. 

[Ahmedx]

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

[Srikantht]

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. 

[Srikantht]

@Ahmedx 

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

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

[Ahmedx]

CAGR = Var matrix1 =  CAGR (% TY v LLY)]
Var matrix2 =
    CALCULATE(CAGR (% TY v LLY)],
REMOVEFILTERS()
    )
Return
SWITCH( TRUE,matrix1 > matrix2, "GREEN", "RED")

[Srikantht]

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