Filter a report using query string parameters in the URL - not working because of &

Question

This is my column name and value below.

ASSET_NAME - mats&service

Below is the dax code i use to get data from table and create a link to filter the my page, but it wont work when value(mats&service) in column(ASSET_NAME) has & in it, the link gets terminated at & like this https:/app.powerbi.com/?filter/somerandomtextand%20andthesemats and the filter wont work, can someone help me escape this & so that value is accepted and filtered in my page,

RawData =
MAX ('Raw data'[Raw Data])&"?filter=Table/ASSET_NAME eq"&" '"
& MAX ( CurrentData[ASSET_NAME] )&"' and Table/LEVEL1 eq"&" '"
& MAX ( CurrentData[LEVEL])&"' and Table/DOS_NO eq"&" ' "
& MAX ( CurrentData[RULE_NO] )&" ' "

Andrey Nikolov · Accepted Answer · 2022-09-09 06:29:39Z

1

The official documentation has a section how to handle special characters in the values. The & character should be replaced with %26, so instead of mats&service value, try with mats%26service.

When concatenating the strings to construct the URL, use SUBSTITUTE DAX function (or multiple nested or separate calls to it) to replace the special characters in the values, e.g. like this:

Measure = 
    var AssetName1 = MAX(CurrentData[ASSET_NAME])
    var AssetName2 = SUBSTITUTE(AssetName1, "%", "%25")
    var AssetName3 = SUBSTITUTE(AssetName2, "+", "%2B")
    var AssetName4 = SUBSTITUTE(AssetName3, "&", "%26")
    RETURN "?filter=Table/ASSET_NAME eq '" & AssetName4 & "'"

Another option is to add a custom column with "safe" values, which are URL encoded using Uri.EscapeDataString M function:

ASSET_NAME_ENCODED = Uri.EscapeDataString([ASSET_NAME])

and use this column when constructing the URL.

edited Sep 9, 2022 at 6:29

answered Sep 8, 2022 at 6:12

Andrey Nikolov

13.5k3 gold badges26 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

evanoruvan Over a year ago

thanks,but this is a dynamic one,i cant change value in table

Andrey Nikolov Over a year ago

@evanoruvan Of course you can! Either use Uri.EscapeDataString to get values, which are safe to be used in the URL, or replace the special characters one by one (starting with %!) by calling SUBSTITUTE function. I've added examples how you can do this in my answer.

Collectives™ on Stack Overflow

Filter a report using query string parameters in the URL - not working because of &

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related