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.
Community ChampionThe first parameter of summarize should be the fact table, not the dim table.
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 🙂
- Fowmy
Super User
Anonymous This my understanding and correct me if I am wrong. You are adding a column to a table using ADDCOLUMNS, it creates a row context but inside the FILTER function, the PRODUCT table is not filtered by the table Color from the SUMMARIZE ( 'Product', 'Product'[Color] ) hence it has all the colors so, it is replaced by "Green" for each color. That is why it shows the value of Green on each row.
________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
CNENFRNLHi, 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.
