Order of Evaluation in DAX

DAX DAX Calculations
1

I was confused after watching this video. The explanation on “Nested Calculate” after 20:53 did not satisfy me.

All the results for FILTER-CALCULATETABLE, FILTER-FILTER, CALCULATETABLE-FILTER were as I expected, but I could not understand CALCULATETABLE-CALCULATETABLE.

CALCULATETABLE(
CALCULATETABLE(
VALUES(‘Product’[Color]),
‘Product’[Color]=“Blue”
),
‘Product’[Color]=“Red”)

Why this returns “Blue”? She says inner CALCULATETABLE overwrites, but why not FILTER-CALCULATETABLE does not?

I would be glad if someone could explain.

2

Hi @saglamtimur

Can you please share the video link…

3

Here it is

Sorry, for forgetting.

4

@saglamtimur
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:

  1. CALCULATE/CALCULATETABLE evaluate filters starting from the outside and moving
  2. If the current filter context and the filter coming from CALCULATE are coming from the same column, CALCULATE overwrites the existing filter.
  3. 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:

FILTER(
	CALCULATETABLE(
			VALUES(‘Product’[Color]),
			‘Product’[Color]=“Blue”
		),
	‘Product’[Color]=“Red”
	)

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.

  1. FILTER merges the current filter context and the filters in FILTER, does not overwrite. Only CALCULATE/CALCULATETABLE can overwrite
4 Likes
5

Fantastic explanation. It’s all clear now. Thank you.

6

I hope that you are having a great experience using the Support Forum so far @saglamtimur. We’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!