CALCULATE/CALCULATETABLE are the only functions that modify the the filter context. With nested CALCULATE/CALCULATETABLE the filtering starts from the outside and moves in. In your example above, we start with the Product Color = Red, then the inner CALCULATETABLE is evaluated and since there is a filter on Product Color (in this case red) and we have an inner CALCULATETABLE of Product Color = Blue the inner Product Color overwrites the existing filter of Product Color = Red. Two things keep in mind:
- CALCULATE/CALCULATETABLE evaluate filters starting from the outside and moving
- If the current filter context and the filter coming from CALCULATE are coming from the same column, CALCULATE overwrites the existing filter.
- If the current filter context and the filter coming from CALCULATE are coming from different columns, then the two filters are merged together.
That only applies to CALCULATE/CALCULATETABLE though. When using Filter, the current filter context and the filter in FILTER are always merged together. So in the example in that video:
You have a table of Product Color = Blue from CALCULATETABLE, then you want to filter that table on Product Color = Red. Since we only have a table of BLUE when we filter that to return only RED, there is nothing returned because it is looking for values that are Blue and Red, which doesnt happen. Instead of Product Color = Red, we could do something like Product Type = “Bike” (or whatever) and it would return all the Bikes which are blue because we would have values that are Blue and Bikes.
- FILTER merges the current filter context and the filters in FILTER, does not overwrite. Only CALCULATE/CALCULATETABLE can overwrite