Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
jeffreyweir
Helper III
Helper III

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

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?

1 ACCEPTED SOLUTION

give this a try 

 

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

 



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


View solution in original post

6 REPLIES 6
richbenmintz
Solution Sage
Solution Sage

Hi @jeffreyweir

Have you tried using the IN operator

 

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

 

 



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


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-...

@jeffreyweir can you share the .pbix?



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


give this a try 

 

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

 



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


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?

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.



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.