top of page Search
• 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:

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

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.

```VAR _CTable =
CALCULATETABLE(
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 =
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 =
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

```VAR _FTable =
FILTER(
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 =
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 =
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!

bottom of page