Understand Nested Calculate dax Code

[han_rj]

Hi All, 

I am exploring nested calculate dax, out of this dax I was expecting only Audio Sales to be displayed but the filter context on Product Category is retained in Measure Calculates measures. Please may I have help understand what I am missing.

DataModel:

[bhanu_gautam · Accepted solution]

@han_rj The issue with your DAX formula is that the nested CALCULATE function does not work as expected because the inner CALCULATE does not return a scalar value that can be used in the outer CALCULATE

Try this

Measures =
VAR calc = CALCULATE([Sales Amount], 'Product Category'[Category] = "Audio")
RETURN calc

[sanalytics · Accepted solution]

@han_rj 
why you are using variable? variable scope is limited..
Just use CALCULATE([Sales Amount], 'Product Category'[Category] = "Audio")
Or 
CALCULATE([Sales Amount],FILTER(ALL( 'Product Category'[Category] ),'Product Category'[Category] = "Audio") )
This is not the case of nested calculate.

Regards

sanalytics

[johnt75 · Accepted solution]

Variables in DAX are not really variable, they are constants. They are only ever evaluated once, and then they do not change. So your calc variable is evaluated in the original filter context, and the subsequent CALCULATE has no effect on that.

[sanalytics · Accepted solution]

@han_rj 
why you are using variable? variable scope is limited..
Just use CALCULATE([Sales Amount], 'Product Category'[Category] = "Audio")
Or 
CALCULATE([Sales Amount],FILTER(ALL( 'Product Category'[Category] ),'Product Category'[Category] = "Audio") )
This is not the case of nested calculate.

Regards

sanalytics

[bhanu_gautam · Accepted solution]

@han_rj The issue with your DAX formula is that the nested CALCULATE function does not work as expected because the inner CALCULATE does not return a scalar value that can be used in the outer CALCULATE

Try this

Measures =
VAR calc = CALCULATE([Sales Amount], 'Product Category'[Category] = "Audio")
RETURN calc