Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

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...

1 ACCEPTED SOLUTION


@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...

View solution in original post

13 REPLIES 13
slap33
Frequent Visitor

just found the answer p.364 of the Definitive Guide to Dax ! Thanks Marco&Alberto !!

slap33
Frequent Visitor

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 😅😅

mattbrice
Solution Sage
Solution Sage
Anonymous
Not applicable

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.

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] ) )
)

FILTER vs CALCULATETABLE.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale

 

 

 

 

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@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!

Hi, I think your next measure should be

Filter_NumOfCities =
COUNTROWS (
   FILTER (
            ALL ( 'Table15'[CITY] ),
            'Table15'[CITY] = EARLIER ( 'Table15'[CITY] )
        )
    )
Anonymous
Not applicable


@mattbrice wrote:

@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!


 

Could you give a short quick example of Filter used inside Calculatetable and Calculatetable used inside of Filter, and state the key points?  I am trying to grasp the best use cases.

 

Thanks in advance!

Anonymous
Not applicable

I find your resonse very helpful in understanding the concept.  Thank you!

Anonymous
Not applicable

Thanks Dale, that *is* interesting.. though, I wish I could better wrap my head around what is happening in your example 🙂

 

* Since the formulas "work"... there must be an EARLIER( ) context, which suggets... a context was created!? 

* Unclear why Santa Monica is different than the others (I assume related to the hidding data?)

There is row context because both look to be calculated columns,...not measures. And of course calculated columns have row context, but only transitioned to filter context with use of CALCULATE or CALCULATETABLE or some other table function.

Anonymous
Not applicable

What if I remove CALCULATETABLE from the language.  What do we lose?


@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...

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.