Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
85 | |
68 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |