Forum Discussion
Helper VHow to use PATHCONTAINS to pass a multi-value parameter in a query in Report Builder
Hi, Fern_21,
Thank you for posting your query in the Microsoft Fabric Community Forum.
The value[items].[facility_code].&[3422] indicates that your query is using a hierarchy/member reference, not the actual scalar value (3422).PATHCONTAINS only works with plain scalar values (text or numbers). It cannot evaluate hierarchy keys, while CONTAINSSTRING still works because it performs a simple string comparison.
To resolve this, make sure your dataset and filters use the base column value (the raw facility code) rather than a hierarchy level. Once the query returns 3422 instead of the hierarchy-formatted value, PATHCONTAINS will work correctly.
Thanks, DataVitalizer for sharing valuable insights.Best regards,
Ganesh Singamshetty.
Super UserHi Fern_21
You need to use PATHCONTAINS with pipe-delimited values by replacing your CONTAINSSTRING filters with PATHCONTAINS:
FILTER(
VALUES('items'[facility_code]),
PATHCONTAINS(@itemsfacilitycode, 'items'[facility_code])
)
The key difference is that PATHCONTAINS expects the first parameter to be a delimited string path (which you already have with the pipe separator from your Join function).
Here is a full example for one filter:
FILTER(
VALUES('items'[facility_code]),
PATHCONTAINS(@itemsfacilitycode, 'items'[facility_code])
),
FILTER(
VALUES('items'[revision]),
PATHCONTAINS(@itemsrevision, 'items'[revision])
)
Just swap out all your CONTAINSSTRING calls with PATHCONTAINS using the same parameters and since you're already using pipe delimiters in your Join, PATHCONTAINS should work directly.
If it still doesn't work, make sure your parameter values don't contain pipes themselves that would break the delimiter logic.
Did it work? 👍 A kudos would be appreciated
🟨 Mark it as a solution to help spread knowledge 💡
Helper VHi DataVitalizer !
I tried this solution with PATHCONTAINS but it doesn't work. Considering that with CONTAINSSTRING I obtain this
How can I solve it?