Forum Discussion
Understand Nested Calculate dax Code
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:
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 calchan_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
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.
3 Replies
- bhanu_gautamSuper User
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 - sanalyticsSuper User
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
- johnt75Super User
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.