Brent Jones
The Best Guide to FILTER() and CALCULATETABLE() Differences

Expanding on the conversation from this blog written by Arpita Ghosh, How to Differ CALCULATETABLE over FILTER in DAX, I wanted to clearly layout the different combinations you can come across, particularly when used with table variables.
Here I demonstrate 6 ways to look at these:
CalculateTable() with AddColumns() Inside
CalculateTable() with AddColumns() Outside
CalculateTable() with AddColumns() Outside with Calculate()
Filter() with AddColumns() Inside
Filter() with AddColumns() Outside
Filter() with AddColumns() Outside with Calculate()

The Alien Says... Do not use #2 or #3. All other methods can be correct depending on your situation, but #2 and #3 just need to be avoided. They return potentially unexpected results as we can see below.
- Synopsis -
Method | Results in... |
1) CalculateTable with AddColumns Inside | Rows filtered. Values filtered. |
2) CalculateTable with AddColumns Outside | Rows unfiltered. Values unfiltered. |
3) CalculateTable with AddColumns Outside with Calculate | Rows unfiltered. Values filtered. |
4) Filter with AddColumns Inside | Rows filtered. Values unfiltered. |
5) Filter with AddColumns Outside | Rows filtered. Values unfiltered. |
6) Filter with AddColumns Outside with Calculate | Rows filtered. Values filtered. |
Filter - Always returns rows filtered. Make sure to wrap CALCULATE() over any calculations you are doing within the table being filtered on.
CalculateTable - has several implications:
If you pass in a table as a variable, you need a CALCULATE() for any calculations on that table.
Any filters used in CalculateTable WILL NOT flow through to a table passed in as a variable.
Any filters used in CalculateTable WILL flow through to a table directly used in CalculateTable, and Filters will also apply to any calculations (you do not need to wrap it in a CALCULATE()).
Here's a diagram to line things up.

Still confused? Take a look at the examples below to clear things up.
1) CALCULATETABLE() with ADDCOLUMNS() Inside
VAR _CTable =
CALCULATETABLE(
ADDCOLUMNS(
VALUES(Products[ColorName]),
"@countofrows", COUNTROWS(Products)
),
Products[ColorName] = "Blue"
)
RETURN
_CTable
Returns the following:
ColorName | @countofrows |
Blue | 200 |
2) CALCULATETABLE with ADDCOLUMNS Outside as a Variable
VAR _MyTable =
ADDCOLUMNS(
VALUES(Products[ColorName]),
"@countofrows", COUNTROWS(Products)
)
VAR _CTable =
CALCULATETABLE(
_MyTable,
Products[ColorName] = "Blue"
)
RETURN
_CTable
Returns the following:
ColorName | @countofrows |
Silver | 2517 |
Blue | 2517 |
White | 2517 |
Red | 2517 |
3) CALCULATETABLE with ADDCOLUMNS Outside as a Variable Using Calculate()
VAR _MyTable =
ADDCOLUMNS(
VALUES(Products[ColorName]),
"@countofrows", CALCULATE(COUNTROWS(Products))
)
VAR _CTable =
CALCULATETABLE(
_MyTable,
Products[ColorName] = "Blue"
)
RETURN
_CTable
Returns the following:
ColorName | @countofrows |
Silver | 417 |
Blue | 200 |
White | 505 |
Red | 99 |
4) FILTER with ADDCOLUMNS Inside
VAR _FTable =
FILTER(
ADDCOLUMNS(
VALUES(Products[ColorName]),
"@countofrows", COUNTROWS(Products)
),
Products[ColorName] = "Blue"
)
RETURN
_FTable
Returns the following:
ColorName | @countofrows |
Blue | 2517 |
5) FILTER with ADDCOLUMNS Outside as a Variable
VAR _MyTable =
ADDCOLUMNS(
VALUES(Products[ColorName]),
"@countofrows", COUNTROWS(Products)
)
VAR _FTable =
FILTER(
_MyTable,
Products[ColorName] = "Blue"
)
RETURN
_FTable
Returns the following:
ColorName | @countofrows |
Blue | 2517 |
5) FILTER with ADDCOLUMNS Outside as a Variable Using CALCULATE()
VAR _MyTable =
ADDCOLUMNS(
VALUES(Products[ColorName]),
"@countofrows", CALCULATE(COUNTROWS(Products))
)
VAR _FTable =
FILTER(
_MyTable,
Products[ColorName] = "Blue"
)
RETURN
_FTable
Returns the following:
ColorName | @countofrows |
Blue | 200 |
Hopefully this clears up some of the differences between CalculateTable and Filter. Cheers!