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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.