DAX: Difference between FILTER and CALCULATETABLE

Home Power BI DAX DAX: Difference between FILTER and CALCULATETABLE

 

In the world of DAX formulas, the two formulas that get mixed up the most are FILTER and CALCULATETABLE. That is due to the fact that they actually do return the same result in certain scenarios. For instance, when a Matrix is created on the canvas in which a filter is applied on one dimension table (in this case by adding it to the rows of this Matrix) and the filter in the DAX formula is applied on another dimension table.

Consider these two formula’s:

 

1.1 Count Sales Product A FILTER =

COUNTROWS (

    FILTER (

        fact_Sales,

        RELATED ( dim_Products[Productkey] ) = “Product A”

    )

)

 

1.2 Count Sales Product A CALCULATETABLE =

COUNTROWS (

    CALCULATETABLE (

        fact_Sales,

        dim_Products[Productkey] = “Product A”

    )

)

 

 

The syntax is actually rather similar, and so are the results when applied in this Matrix:

The engine, however, did evaluate these two formulas in a different way. 1.1 is executed in a nested row context (provided for by the FILTER formula) within a filter context (provided for by the measure).  

1.2, however, is executed within an altered filter context since the filter in the CALCULATETABLE statement is added to the initial filter context which only is populated by the Monthname coming out of the Row Header. If you’re not sure on Row and Filter Context do make sure to watch my video’s on those concepts.

 

Now let’s see when FILTER and CALCULATETABLE start deviating.

 

Consider these formula’s:

 

2.1 Count Sales March FILTER =

COUNTROWS (

    FILTER (

        fact_Sales,

        RELATED ( ‘Calendar'[Monthname] ) = “March” && 

            RELATED (‘Calendar'[Month] ) = 3

    )

)

 

2.2 Count Sales March CALCULATETABLE =

COUNTROWS (

    CALCULATETABLE (

        fact_Sales,

        ‘Calendar'[Monthname] = “March”,

        ‘Calendar'[Month] = 3

    )

)

 

2.1 is simply going to filter down the fact sales table to just March, so if a filter coming out of the Row Header is on another Month then a filter is applied on that other month and on March resulting in a blank.

 

2.2 on the other hand is going to overwrite the Monthname coming out of the row header in the filter context, so it will always return the value for March regardless of the value in the row header.

And now let’s look at a more profound difference between the two. Again, consider these formulas:

 

3.1 Count Sales Previous Month FILTER =

COUNTROWS (

    FILTER (

        fact_Sales,

        PREVIOUSMONTH ( ‘Calendar'[Date] )

    )

)

 

3.2 Count Sales Previous Month CALCULATETABLE =

COUNTROWS (

    CALCULATETABLE (

        fact_Sales,

        PREVIOUSMONTH ( ‘Calendar'[Date] )

    )

)

 

 

Since CALCULATETABLE is used in 3.2, the initial filter context can get overwritten by what has been specified in the filter statement, so: PREVIOUSMONTH ( ‘Calendar'[Date] ). In this formula, DAX is making sure that the Monthname in the initial filter context gets overwritten by the month before it.

FILTER is unable of overwriting the initial filter context so trying that would give an error.

The last but certainly not least difference is the fact that CALCULATETABLE, just like CALCULATE, will trigger context transition. So, if a formula is initially evaluated within row context, that row context will get converted into a filter context. In order to show this behavior, calculated columns work well as the evaluation context of a calculated column is row context. Consider these 2 columns which we’ll add to the dimension Clients table:

 

4.1 Column Count Sales Product A FILTER =

COUNTROWS (

    FILTER (

        fact_Sales,

        RELATED ( dim_Products[Productkey] ) = “Product A”

    )

)

 

4.2 Column Count Sales Product A CALCULATETABLE =

COUNTROWS (

    CALCULATETABLE (

        fact_Sales,

        dim_Products[Productkey] = “Product A”

    )

)

 

4.1 will not perform any context transition, the evaluation context will remain row context. Row context does not filter. I repeat: row context does not filter. So we’ll simply get a count of all of Product A sales returned, for all clients.

4.2, however, will perform context transition. So first a filter on the client in the data model will get applied and afterwards the evaluation of the formula is done. Therefore, this will result into the count of product A by client.

Would you like to become a Power BI Pro? Minova offers Power BI incompany trainings for all levels, from beginner to advanced. Please refer to the trainings page for more information