The use of CALCULATETABLE vs FILTER as a filter argument

DAX DAX Calculations
1

Hi All.

I came across a video last week from Curbal that presented a discussion on the use of CALCULATETABLE in lieu of FILTER in many DAX expressions.

The video has a noticeable number of views and comments so is of interest to the community.

In that video, there was also a discussion of a Microsoft document that espoused avoiding the use of FILTER as a filter argument.

I don’t have an opinion on this myself, but, seeing as this is the first time I’ve come across this in the last 2 years and have personally experienced and created many successful Power BI reports that use FILTER as a filter argument in DAX measures, imagine this may only become an optimization issue when dealing with quite large datasets.

I’m creating this post as a starter for a discussion and encourage the forum at large to share their thoughts, especially those experienced in DAX optimization.

Greg

2

I saw that video over the weekend, @Greg, and I was very surprised. Filter had been pounded into me from the earliest training in DAX, and I know that if I go back and review my early models, it’s all over the place.

I will admit that it’s not in many of my newer models, not because I tried to stay away from it, but lately I’m dealing with more complex questions, so I don’t lean on it as much.

I do have a large model that is used for A LOT of my reports, and I have been intending for a while to go back and rebuild it with some of the newer techniques I’ve learned (once I practice some more with some of them). It needs some optimization, but I’ve been putting off cleaning it up because ‘it works’ for now (even if it could be cleaner). This reveal might be the kick I need to actually see what I can do to rebuild the M and DAX for a cleaner over all model.

3

@heather,

Same here - I watched it on Friday and it blew my mind. Not only do I use FILTER as a filter argument with CALCULATE, I frequently use it by itself it to create virtual table variables. I’d be curious to know how much difference it actually makes re: performance to know how much emphasis I should put on mentally retraining myself to use CALCULATETABLE instead of FILTER…

  • Brian
4

I would say you hold your horses before making any switch because both are equally useful:

in the example she has used:

=
CALCULATE (
    [All Sales],
    CALCULATETABLE ( Table, Table[Color] = "Red", Table[Sales] > 1 )
)

Even though to our eyes there appears to be some optimization in terms of filtering the table first and then injecting the filters, I can’t say how much optimization would there be without testing. Why use a full table when the goal can be accomplished by just column filters, here I mean using column filters directly on the measure through outer CALCULATE and not on the ‘Table’ using CALCULATETABLE

I agree that using full table as a filter is a bad practice, but replacing it with the above CALCULATETABLE syntax won’t help much.

A good version of the same code would be as we all know already:

Either you prepare 2 separate filters of 2 columns

=
CALCULATE ( [All Sales], Table[Color] = "Red", Table[Sales] > 1 )

or a single filter of 2 columns:

=
CALCULATE (
    [All Sales],
    FILTER (
        ALL ( Table[Color], Table[Sales] ),
        Table[Color] = "Red"
            && Table[Sales] > 1
    )
)

In general FILTER has its own benefits when creating a calculated column, since there is no implicit Context Transition, that’s one less thing to worry about.

2 Likes
5

Just including some other references for those who are interested in this topic.

.
And in the documentation here, you’ll find more links to relevant articles: