Forum Discussion
FILTER vs CALCULATETABLE
I'm about to lose some serious street cred, but so be it... :)
I use FILTER( ) all the time and CALCULATETABLE( ) almost never. Is there some time I *should* be using CALCULATETABLE? What is this difference in these 2 functions?
If possible, would love a crisp example of them returning different results...
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...
13 Replies
mattbriceSolution Sage
How about this from the best:
https://www.sqlbi.com/articles/filter-vs-calculatetable-optimization-using-cardinality-estimation/
Ya, of course I went to see what marcorusso had to say! :) But ... somehow it didn't help me. At least from a pure functionality perspective -- as that seems to do more w/ performance.
And yet, I'm not convinced they are functionally equivalent.
v-jiascu-msftMicrosoft Employee
Hi Anonymous
"Filter" can generate a row context while "Calculatetable" can't. In the example below, the formulas are very similar. Both work but bring us different results. Hope this would help.
Filter_NumOfCities = COUNTROWS ( FILTER ( 'Table15', 'Table15'[CITY] = EARLIER ( Table15[CITY] ) ) )
Calculatetable_NumOfCities = COUNTROWS ( CALCULATETABLE ( 'Table15', 'Table15'[CITY] = EARLIER ( 'Table15'[CITY] ) ) )Best Regards!
Dale
slap33Helper I
7 years later.... 😂
i was wondering why CALCULETABLE is advised against FILTER although CALCULETABLE is actually built on... FILTER !
saying it another way : if i wanna avoid using FILTER, by using CALCULATETABLE, i end up using FILTER because i did use CALCULATETABLE 😅😅- slap33
just found the answer p.364 of the Definitive Guide to Dax ! ✅ Thanks Marco&Alberto !!