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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
iamprajot
Responsive Resident
Responsive Resident

FILTER and CALCULATETABLE with ALL giving different result.

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.

 

 

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

3 REPLIES 3
Greg_Deckler
Super User
Super User

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

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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