Forum Discussion
Losing context transition when using CALCULATE inside ADDCOLUMNS
Hello,
I have executed the below command in DAX Studio. As you can see I am introducing the column 'Product'[Color] in the rows (in SUMMARIZE) and then I define 3 different measures.
EVALUATE
ADDCOLUMNS (
SUMMARIZE ( 'Product', 'Product'[Color] ),
"Product Sales Green",
CALCULATE ( SUM ( 'Sales'[NetAmount] ), FILTER ( 'Product', 'Product'[Color] = "Green" ) ),
"Product Sales Green v2",
CALCULATE ( SUM ( 'Sales'[NetAmount] ), FILTER ( VALUES( 'Product'[Color] ), 'Product'[Color] = "Green" ) ),
"Product Sales Green v3",
CALCULATE ( SUM ( 'Sales'[NetAmount] ), FILTER ( ALL( 'Product'[Color] ), 'Product'[Color] = "Green" ) )
)
My issue lies in the output. I would have expected the first 2 measures to retain the filter coming from the rows (i.e. from the 'Product'[Color] ) so that it will have blank for all rows apart from when 'Product'[Color] = "Green" . On the other hand in the 3rd measure I would have expected to see the sales of Green products in all rows as using ALL() would have removed the context transition coming from the rows.
| Product.Color | Product Sales Green | Product Sales Green 2 | Product Sales Green v3 |
| Blue | 10.44 | 10.44 | 10.44 |
| Green | 10.44 | 10.44 | 10.44 |
| Black | 10.44 | 10.44 | 10.44 |
| Orange | 10.44 | 10.44 | 10.44 |
| White | 10.44 | 10.44 | 10.44 |
| …. | …. | …. | …. |
In other words I would have expected the below result:
| Product.Color | Product Sales Green | Product Sales Green 2 | Product Sales Green v3 |
| Blue | 10.44 | ||
| Green | 10.44 | 10.44 | 10.44 |
| Black | 10.44 | ||
| Orange | 10.44 | ||
| White | 10.44 | ||
| …. | …. | …. | …. |
Can someone please explain to me what am I missing. Is it Dax Studio, is it the fact that I use ADDCOLUMNS and SUMMARIZE or is it something else?
Thanks in advance.
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.
6 Replies
- MattAllington
Community Champion
The 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 🙂
- Ashish_Mathur
Hi,
Could you share some data, describe the question and show the expected result.
