Forum Discussion

han_rj's avatar
han_rj
Helper IV
1 year ago
Solved

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 calc

  • 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

  • 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

  • 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

  • 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

  • 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.