Forum Discussion
Losing context transition when using CALCULATE inside ADDCOLUMNS
Hi, Anonymous , I think you came across almost the exactly same problem mentioned in this article: https://www.sqlbi.com/articles/context-transition-and-expanded-tables/
I totally agree with Fowmy . The issue is all about the very tricky way FILTER() evaluates within a CALCULATE(); to be exact, what on earth the filter context is, in which FILTER() evaluates. As mentioned in the above-referenced article,
CALCULATE executes context transition, but its filter parameters get evaluated in the original filter context, not in the one modified by CALCULATE.
Hello MattAllington ,
If I understand correctly you suggest changing the 3rd line of the code from SUMMARIZE ( 'Product', 'Product'[Color] ) to SUMMARIZE ( 'Sales', 'Product'[Color] )?
I have run the code with that change but I still get the first (incorrect) output from my original post. The only way I have figured out to get the second (correct) output is to define the measure as:
CALCULATE ( SUM ( 'Sales'[NetAmount] ),
KEEPFILTERS (
FILTER ( 'Product', 'Product'[Color] = "Green" )
)
I can also add the first 2 measures in the data model (and process them) and then go to PowerBI Desktop and add them in a pivot table (with 'Product'[Color] in rows and the measures as values) and I also get the correct output.
The thing that I cant explain though is why the first 2 measures in my code (in my original post) do not follow that expected behaviour. It is way above my DAX literacy 🙂
Community ChampionHi, Anonymous , I think you came across almost the exactly same problem mentioned in this article: https://www.sqlbi.com/articles/context-transition-and-expanded-tables/
I totally agree with Fowmy . The issue is all about the very tricky way FILTER() evaluates within a CALCULATE(); to be exact, what on earth the filter context is, in which FILTER() evaluates. As mentioned in the above-referenced article,
CALCULATE executes context transition, but its filter parameters get evaluated in the original filter context, not in the one modified by CALCULATE.