Different filter behaviors in SUMMARIZECOLUMNS and CALCULATETABLE

We recently updated SUMMARIZECOLUMNS on DAX Guide by adding an example that clarifies the difference between a filter applied to SUMMARIZECOLUMNS and a filter applied to CALCULATETABLE.

Most of the times, you can move a filter from a SUMMARIZECOLUMNS argument

SUMMARIZECOLUMNS ( 
    'Table'[column],
    __FilterTable,
    "Measure", [Measure]
)

to an outer CALCULATETABLE

CALCULATETABLE (
    SUMMARIZECOLUMNS ( 
        'Table'[column],
        "Measure", [Measure]
    ),
    __FilterTable
)

and notice no differences in the results. However, there is a difference that can produce different query results: for example, when SUMMARIZECOLUMNS performs no aggregations.

SUMMARIZECOLUMNS ( 
    'Table'[column],
    __FilterTable
)

The filter applied to SUMMARIZECOLUMNS only affects the columns of the same table used as groupby in SUMMARIZECOLUMNS. Thus, if we use Sales[Order Date] in the first argument of SUMMARIZECOLUMNS, then only filters applied directly on columns of the Sales table have effects on the values of Sales[Order Date] returned to SUMMARIZECOLUMNS. A filter on the Date table does not have any effect on this evaluation, regardless of the relationship between Date and Sales and the fact that Date is part of the Sales expanded table. The same filter applied on Date by using CALCULATETABLE propagates to the Sales table as because of the relationship existing between the two tables.

The behavior is not different when you have aggregation in SUMMARIZECOLUMNS. Still, in that case, the elimination of blank results typically produces the same result as if you used the same filter in a CALCULATETABLE surrounding SUMMARIZECOLUMNS.

The following example that we added to DAX Guide clarifies the difference with a specific example you can test online by using DAX.do.

-- This query returns 0 rows, because all the Sales transactions
-- have an Order Date that exists in Date
EVALUATE
CALCULATETABLE (
    VALUES ( Sales[Order Date] ),
    TREATAS ( { BLANK () }, 'Date'[Date] )
)
ORDER BY Sales[Order Date]

-- This query returns a list of all the unique values in Sales[Order Date]
-- because the filter applied to 'Date'[Date] has only effects on the Date table
-- The Sales table is unaffected by that filter.
EVALUATE
SUMMARIZECOLUMNS (
    Sales[Order Date],
    TREATAS ( { BLANK () }, 'Date'[Date] )
)
ORDER BY Sales[Order Date]

-- This query returns 0 rows, because COUNTROWS ( Sales ) 
-- always returns blank, being executed in a filter context 
-- that includes 'Date'[Date] and Sales[Order Date]
-- Because there are no rows in Sales with an invalid Order Date,
-- there are no rows with a non-blank result for COUNTROWS ( Sales )
EVALUATE
SUMMARIZECOLUMNS (
    Sales[Order Date],
    TREATAS ( { BLANK () }, 'Date'[Date] ),
    "# Transactions", COUNTROWS ( Sales )
)
ORDER BY Sales[Order Date]

While it is unlikely this difference can cause any issue in your daily work, it is something you must be aware of when you use SUMMARIZECOLUMNS without aggregations.