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:

1. CalculateTable() with AddColumns() Inside

2. CalculateTable() with AddColumns() Outside

3. CalculateTable() with AddColumns() Outside with Calculate()

4. Filter() with AddColumns() Inside

5. Filter() with AddColumns() Outside

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