How to pass filters using embed query parameters where column having multiple values in it.

[Jyo24]

My Data set is as below - 

178	Gmbh	NULL
179	Aashima	|178|
180	Neeraj	|179|178|
181	Tripti	|180|179|178|
182	Harman	|179|178|
183	Vivek	|182|179|178|

If |179| is passed from first column in URL filter then it will search in third column in dataset for this value and will return rows in which this value is present.
ex:  |179| is passed:

Filtered rows are:

I have applied below steps but applying filter is not working or may be incorrect.

Way to pass Query string parameter in report:
1. Open the attached .pbix report in PowerBI desktop.
2. Upload/Publish the report in desired workspace in PowerBI service,
3. Open the report in PowerBI service.
4. Click on file menu and select Website or Portal option under Embed report, to copy the report URL.

5. Copy the selected URL , like below:

6. Modify the copied URL, apply filter like below:
https://app.powerbi.com/reportEmbed?reportId=2cc7f9fd-07f4-4659-87f7-a75a9b920bff&autoAuth=true&ctid... eq ‘|178|’

TableName= Dataset Name

can anyone please help me in applying the correct filter?

[v-kkf-msft · Accepted solution]

Hi @Jyo24 ,

You need to escape the character | to %7C, like this.

&$filter=SampleData/Values eq '%7C178%7C'

https://app.powerbi.com/reportEmbed?reportId=43axxxxxxx1b&autoAuth=true&ctid=41xxxxxf54&config=eyJjbHVzdGVyVXJsIjoiaHR0cHM6Ly93YWJpLXVzLWNlbnRyYWwtYS1wcmltYXJ5LXJlZGlyZWN0LmFuYWx5c2lzLndpbmRvd3MubmV0LyJ9&$filter=SampleData/Values eq '%7C178%7C'

But you can't filter out all paths containing '178' in the URL, you can only filter out rows with the value '|178|'.

If you want to use URL parameter to filter all rows, please recreate your model.

1. create a new table.

2. Create measures and filter visual.

SelectID = SELECTEDVALUE ( IDs[ID] )

IsContain = SEARCH ( [SelectID], MAX ( SampleData[Value] ), , BLANK () )

3. Embed report.

https://app.powerbi.com/reportEmbed?reportId=43xxxx741b&autoAuth=true&ctid=416exxxxxb0f54&config=eyJjbHVzdGVyVXJsIjoiaHR0cHM6Ly93YWJpLXVzLWNlbnRyYWwtYS1wcmltYXJ5LXJlZGlyZWN0LmFuYWx5c2lzLndpbmRvd3MubmV0LyJ9&$filter=IDs/ID eq 179

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ibarrau]

Hi. I haven't tried this scenario before but as far as I can remember the issue might be in the "Pipes". Those might be considered special characters. The doc says that you should use unicode for special characters. Then look for the unicode for the pipe and try building the url with that. (might be U+01C0, but look for it because i'm not sure )

You can read about that here: https://docs.microsoft.com/en-us/power-bi/collaborate-share/service-url-filters#special-characters-i...

I hope that helps,

[Jyo24]

I tried with the above solution and modified the URL as below, but it is not working - 

DCS-630SilverManagerDownline - Power BI

But instead of tablename, can we pass the sheet name if data is in excel sheet.

[ibarrau]

You can't filter by the name of the sheet. You must do it by the name of the table in power bi and the column in there. If you use the name of the sheet as the table in Power Bi it might help you, but just remember it's the name of the table in Power Bi. It should be something like:

?filter=Table/Column eq 'Value in single quotes'

I hope that helps,