TREATAS can't be used as <Filter> argument of FILTER function?

[jeffreyweir]

I'd like to filter a table based on a disconnected slicer, similar to what I'm trying to do at this thread but using FILTER instead of CALCULATETABLE given the issues outlined at that other thread. 

I thought that the TREATAS function could be used as the <filter> arg of the FILTER function, given that it "applies the result of a table expression as filters to columns from an unrelated table". But the following returns an error:

COUNTROWS (
    FILTER (
        ALL ( CTE ),
        TREATAS ( VALUES ( 'Ethnicity Matrix'[Code] ), CTE[EthnicCode] )
    )
)

MdxScript(Model) (1136, 10) Calculation error in measure 'CTE'[Test]: A table of multiple values was supplied where a single value was expected.

Swapping FILTER for CALCULATETABLE doesn't give me the same error (but then I have the problem that the ALL gets evaluated last, wiping out the very filter I just applied). So there doesn't seem to be any problem with my syntax.

Anyone have any wisdom here?

[richbenmintz]

Hi @jeffreyweir

Have you tried using the IN operator

CALCULATE(COUNTROWS(ALL(CTE), CTE[EthicCode] IN VALUES ('Ethnicity Matrix'[Code]))

[jeffreyweir]

I need to return a Table. I've tried CALCULATETABLE but run into different issues as per https://community.powerbi.com/t5/Desktop/CALCULATETABLE-ALLEXCEPT-ditches-column-completely-instead-...

[richbenmintz]

@jeffreyweir can you share the .pbix?

[richbenmintz · Accepted solution]

give this a try 

CALCULATETABLE(filter( 
        ALL(CTE), 
            CTE[EthnicCode] IN VALUES('Ethnicity Matrix'[Code])
            
        )
    )

[jeffreyweir]

The IN function seems to be the perfect workaround. Let me test a little more before accepting/kudos.

I didn't even know it existed. Which has got me wondering...why do we even need TREATAS?

[richbenmintz]

Hi @jeffreyweir

The following blog, https://www.sqlbi.com/articles/propagate-filters-using-treatas-in-dax/, by Marco Russo describes the benefits of the TREATAS function. If you provide you .pbix I could try to get a more optimized solution with TREATAS.