FILTER and CALCULATETABLE with ALL giving different result.

[iamprajot]

Hi,

I have a Sample data Set and I tried to create another two filtered Tables A and Tables B with ALL and with same filtering conditions but A using FILTER and B using CALCULATETABLE.

Table A = FILTER(ALL(Data),Data[Region]="Central" && Data[Customer]="Andrews") 

Table B = CALCULATETABLE(ALL(Data),Data[Region]="Central",Data[Customer]="Andrews")

FILTER's result was a filtered Table but CALCULATETABLE's result was a the complete Table without filtering.

I need to know what was the reason and what is the actual difference at practical level.

[Greg_Deckler]

Well, for some reason the DAX function reference seems to be offline or moved or something, but here would be my take.

The CALCULATETABLE function evaluates a table expression in a context modified by filters. So, the table expression in your formula is ALL(table). When you evaluate this with some filter, the evaluation is still ALL(). Effectively this ALL() trumps the filter because ALL is ALL, regardless of filters. ALL effectively removes any filters. So if you evaluate ALL in the context of ANY filter, it's still ALL. In other words, think of CALCULATETABLE as "evaluate this table expression with these filters applied".

FILTER works differently. Filter returns a table that has been modified by the specified filters. So, filter starts with a table and then applies filters to it versus the way CALCULATETABLE works, which is by taking the filters and applying them to a table expression.

An easy way to summarize this is to think of the steps involved:

CALCULATETABLE

1. Take the filters
2. Apply the filters to the table expression

FILTER

1. Evaluate the table expression
2. Apply the filters

[iamprajot]

I am trying to read it over and over and trying to understand the difference.
What I understood till now is "evaluation of expression" and "applying the filter" is what's happening vice versa but still don't know why are they different, why one should be used over the other and why was CALCULATETABLE came to place when we already had FILTER.

[Greg_Deckler · Accepted solution]

CALCULATETABLE is very handy to preserve all existing filters except for one or two modifications. For example, if trying to calculate something like % change of market share quarter over quarter for example. You can do something like:

VAR __tmpTable = 
SUMMARIZE(
   CALCULATETABLE('Table',ALL('Table'[Year]),ALL('Table'[Quarter]),ALL('Table'[Category])),
   [Category],
   [Year],
   [Quarter],
   "__Measure",
   [Market Share by Category Measure]
)

You can use that as part of a measure calculation. The beauty of this approach is that this preserves any filters that you might have on other columns in that table.

Technically, CALCULATETABLE is a synonum for RELATEDTABLE.

The CALCULATETABLE function changes the context in which the data is filtered, and evaluates the expression in the new context that you specify. For each column used in a filter argument, any existing filters on that column are removed, and the filter used in the filter argument is applied instead.

Filter doesn't work that way, it uses a table expression and THEN applies filters.