Forum Discussion
FILTER vs CALCULATETABLE
Anonymous wrote:What if I remove CALCULATETABLE from the language. What do we lose?
CALCULATETABLE triggers context transition whereas FILTER does not. Andy by itself, FILTER creates a row context whereas CALCULATETABLE does not. But other than these, it is a question for Marco if he is lurking around out there...potential performance issues per his article I referenced would be one thing I would think...
v-jiascu-msft I think the reason for the different results is because of the context transition caused by CALCULATETABLE, not because of the creation of the row context. Just because you don't write an explict FILTER doesn't mean it isn't being used by Dax. Internally I believe the CALCULATETABLE expression you wrote as:
Calculatetable_NumOfCities =
COUNTROWS (
CALCULATETABLE ( 'Table15', 'Table15'[CITY] = EARLIER ( 'Table15'[CITY] ) )
)internally gets rewritten by Dax engine as:
Calculatetable_NumOfCities =
COUNTROWS (
CALCULATETABLE (
'Table15',
FILTER (
ALL ( 'Table15'[CITY] ),
'Table15'[CITY] = EARLIER ( 'Table15'[CITY] )
)
)
)the difference being, as calucated columns, the filter context that gets transitioned.
your 'Filter_NumOfCities' calc column has a row context but no filter context so filters the entire table down to rows where Table15[City] = "Compton" (for first and second row, LOS ANGELES for next 3, and so on).
your 'Calculatetable_NumOfCities' calc column transitions in (because of CALCULATETABLE) all the values for all the columns for the current row except for 'City' which is being blocked by the ALL generated by the Dax engine. So the columns you masked are transitioned into the filter contex. The masked and not seen columns are filtering the table down to where only one row is left, except for SANTA MONICA which must have two identical rows.
per my understanding, this is how the Dax engine works...any comments are welcome!
I find your resonse very helpful in understanding the concept. Thank you!