Forum Discussion
How to use PATHCONTAINS to pass a multi-value parameter in a query in Report Builder
Hi all!
I've a very heavy query in a Power BI Report Builder report. I tried to make it lighter by introducing parameters selected by the user.
Since the parameters are multi-value, I mapped them inside the query using: =Join(Parameters!itemsfacilitycode.Value, "|")
and I used CONTAINSSTRING for filtering: EVALUATE
SUMMARIZECOLUMNS (
'items'[facility_code],
'items'[module_code],
'items'[revision],
'items'[subgroup_description],
'items'[item_tag_code],
'tabella_fatti'[weight_combination],
'items'[main_group_code],
'items'[sequential_number],
'items_reference'[revision],
'items'[remarks],
'items'[data_source_code],
'items'[discipline_description],
FILTER(VALUES('items'[facility_code]), CONTAINSSTRING(@itemsfacilitycode, 'items'[facility_code])),
FILTER(VALUES('items'[revision]), CONTAINSSTRING(@itemsrevision, 'items'[revision])),
FILTER(VALUES('items_reference'[revision]), CONTAINSSTRING(@Revprev, 'items_reference'[revision])),
FILTER(VALUES('tabella_fatti'[weight_combination]), CONTAINSSTRING(@Combination, 'tabella_fatti'[weight_combination])),
FILTER(VALUES('items'[module_code]), CONTAINSSTRING(@itemsmodulecode, 'items'[module_code])),
"OperativeTableDetailCurrent", [OperativeTableDetailCurrent],
"NetDryWeightMeasure", [NetDryWeightMeasure],
"Allowance_TableDetails", [Allowance_TableDetails],
"Gross Dry Weight (t)", [Gross Dry Weight (t)],
"Content_FacilityReport", [Content_FacilityReport],
"XcogCurrent", [XcogCurrent],
"YCog_Current", [YCog_Current],
"Zcog_Current", [Zcog_Current],
"Net_Weight_Details", [Net_Weight_Details],
"Allowance_Details", [Allowance_Details],
"Gross Weight_Details", [Gross Weight_Details],
"ContentMeasureTableDetails", [ContentMeasureTableDetails],
"Operative_Details", [Operative_Details],
"Difference_NetWeight_TableDetails", [Difference_NetWeight_TableDetails],
"DifferenzaAllowance", [DifferenzaAllowance],
"DifferenceGrossWeightDetails", [DifferenceGrossWeightDetails],
"DifferenzaContent", [DifferenzaContent],
"DifferenzaOperative", [DifferenzaOperative],
"DifferenzaXcog", [DifferenzaXcog],
"DifferenzaYCoG", [DifferenzaYCoG],
"DifferenzaZcog", [DifferenzaZcog]
)
Considering that with CONTAINSSTRING I obtain this
how can I modify this query so that it works correctly using PATHCONTAINS? So far, I haven’t been able to get it to work.
Thanks
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.
5 Replies
Hi 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 💡Hi DataVitalizer !
I tried this solution with PATHCONTAINS but it doesn't work. Considering that with CONTAINSSTRING I obtain this[items].[facility_code].&[3422] instead of 3422.
How can I solve it?
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.Hello Fern_21,
We hope you're doing well. Could you please confirm whether your issue has been resolved or if you're still facing challenges? Your update will be valuable to the community and may assist others with similar concerns.
Thank you.
Hello Fern_21,
Hope everything’s going great with you. Just checking in has the issue been resolved or are you still running into problems? Sharing an update can really help others facing the same thing.
Thank you.
