CODEWITHSUNDEEP
powerbidaxpowerquerypowerbi-desktopdaxstudio
evanoruvan
evanoruvan

Reputation: 1

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

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] )&" ' "

Upvotes: -1

Views: 886

Answers (1)

Andrey Nikolov
Andrey Nikolov

Reputation: 13450

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.

enter image description here

Upvotes: 1

Related Questions