Forum Discussion

Jyo24's avatar
Jyo24
New Member
4 years ago
Solved

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

My Data set is as below - 

178GmbhNULL
179Aashima|178|
180Neeraj|179|178|
181Tripti|180|179|178|
182Harman|179|178|
183Vivek|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=cffa1ae5-a2db-44a7-84d2-d1df5908dfbb&config=eyJjbHVzdGVyVXJsIjoiaHR0cHM6Ly93YWJpLXdlc3QtZXVyb3BlLWQtcHJpbWFyeS1yZWRpcmVjdC5hbmFseXNpcy53aW5kb3dzLm5ldC8ifQ%3D%3D&filter=TableName/Upline eq ‘|178|’

TableName= Dataset Name

 

can anyone please help me in applying the correct filter?

Need Help
  • v-kkf-msft's avatar
    v-kkf-msft
    4 years ago

    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.

4 Replies

  • ibarrau's avatar
    ibarrau
    Icon for Super User rankSuper User
    4 years ago

    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-in-url-filters

    I hope that helps,

      • ibarrau's avatar
        ibarrau
        Icon for Super User rankSuper User
        4 years ago

        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,

  • v-kkf-msft's avatar
    v-kkf-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    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.