Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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?
Solved! Go to Solution.
give this a try
CALCULATETABLE(filter( ALL(CTE), CTE[EthnicCode] IN VALUES('Ethnicity Matrix'[Code]) ) )
Proud to be a Super User!
Hi @jeffreyweir
Have you tried using the IN operator
CALCULATE(COUNTROWS(ALL(CTE), CTE[EthicCode] IN VALUES ('Ethnicity Matrix'[Code]))
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?
Proud to be a Super User!
give this a try
CALCULATETABLE(filter( ALL(CTE), CTE[EthnicCode] IN VALUES('Ethnicity Matrix'[Code]) ) )
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.
Proud to be a Super User!
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
137 | |
70 | |
64 | |
52 | |
50 |
User | Count |
---|---|
208 | |
91 | |
62 | |
59 | |
56 |