SUMMARIZECOLUMNS' filters overrides ALLEXCEPT?

[PtrckG]

Hi guys,

I noticed a very strange behaviour of SUMMARIZECOLUMNS, and I replicated the behaviour in a single table model, as attached. The table is as below:

ID	Brand
1	a
2	a
3	b
4	b
5	a

When I ran the following DAX query, I tought that ALLEXCEPT would remove the filter on [Brand] column and only pass on [ID]<>1 to COUNTROWS and that the result should be 4. But actual result is 2, which is based on the red rows.

EVALUATE
	VAR __DS0FilterTable = TREATAS({"a"}, 'Table'[Brand])
	VAR __DS0FilterTable2 = 
		FILTER(
			KEEPFILTERS(VALUES('Table'[ID])),
			NOT('Table'[ID] IN {1})
		)
RETURN
SUMMARIZECOLUMNS(
    __DS0FilterTable, 
    __DS0FilterTable2,
    "Result",
    CALCULATE(
        COUNTROWS('Table'),
        ALLEXCEPT( 'Table', 'Table'[ID] )
    )
)

ChatGPT says:

The SUMMARIZECOLUMNS function constructs its result based on the filters passed to it, before evaluating the CALCULATE inside it. This means that even if CALCULATE removes the Brand filter via ALLEXCEPT, SUMMARIZECOLUMNS is still operating with Brand = "a" in its filter context.

I don't quite understand this explanation, because SUMMARIZECOLUMNS should just provide filter context to CALCULATE, which decides what the final filter context would be for COUNTROWS, right? The actual result seems to show ALLEXCEPT doesn't have any effect at.

Does anyone understand why the result is 2?

[bhanu_gautam]

@PtrckG The SUMMARIZECOLUMNS function creates a filter context based on the filters passed to it. In your case, __DS0FilterTable filters the Brand column to "a", and __DS0FilterTable2 filters the ID column to exclude 1.

The ALLEXCEPT function does not remove the Brand filter because SUMMARIZECOLUMNS applies it first, and ALLEXCEPT only removes filters on columns not specified in its arguments. Therefore, the Brand filter remains, leading to the result of 2.

[PtrckG · Accepted solution]

Thanks @bhanu_gautam, I finally figured out what you meant by "SUMMARIZECOLUMNS applies it first".

Alberto Ferrari's article Understanding DAX Auto-Exist explained this really well. Basically, what SUMMARIZECOLUMNS passes on to CALCULATE is (2, a) and (5,a) due to auto-exist. ALLEXCEPT removes [Brand]=a and keeps [ID] in {2,5} for COUNTROWS.