Optimize the Dax Query

Optimize the Dax QueryFollow
Sree Sree
Sree SreeMar 14, 2023 01:46 AM
Hi, Can anyone optimize the below query. it’s taking 15 seconds to load when I run in Dax studio. and …
1. Optimize the Dax Query

Sree Sree
Posted Mar 14, 2023 01:46 AM
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.

——————————
Sree Sree
Software developer
Bangalore SD
——————————

2. RE: Optimize the Dax Query

Bronze Contributor
James Watts
Posted Mar 14, 2023 06:38 AM
It looks like you want Matrix2 to be calculated once for all data – if you make it a calculated column, it should all speed up.

——————————
James Watts
Business Intelligence Analyst
——————————

3. RE: Optimize the Dax Query

Sree Sree
Posted Mar 14, 2023 07:40 AM
Thanks James for your response.

I want it in measure not as calculated column. and data loading from live connection (SSAS model.). if I create calculated column in model it occupies memory. So I need it in measure.

Thanks

——————————
Sree Sree
Software developer
Bangalore SD
——————————

4. RE: Optimize the Dax Query

Vilmar Santos
Posted Mar 14, 2023 08:57 PM
See if changing ALL by REMOVEFILTERS helps you

——————————
Vilmar Santos
——————————

5. RE: Optimize the Dax Query

Sree Sree
Posted Mar 16, 2023 05:41 AM
Thanks Santos,

Applied REMOVEFILTERS but not gives better performance. This is also executed same duration in runtime.

——————————
Sree Sree
Software developer
Bangalore SD
——————————