top of page
Antares Studio Games Icon_Small.png

The Antares Alien

Power BI   |  Game Development

  • Writer's pictureBrent 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:

  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!

  • Facebook
  • Twitter
  • Instagram
bottom of page