Dynamic embedded URLs in Power BI
Yes, the title sounds kind of complicated... This one is not very commonly used, so I decided to do this blog post to give you some ideas on how you can use parameterised URLs in Power BI.
Things we are going to talk about in this post:
> Build a table with dynamic URLs for each row using dynamic embedded URLs
> Dashboards with dynamic/custom URLs
Because it's always better to see things in action, let's not waste any time and go to the examples 😁
Build a table with dynamic URLs for each row using dynamic embedded URLs
Did you know there is a way you can drill through to another page from a table without having to right click on a row?
I present to you: Dynamic Embedded URLs!
In addition to the report filter options in Power BI Desktop, you can also apply filters to published Power BI reports via the URL string. Rather than having a drill through or multiple report pages with distinct filter conditions, you can use URL links with unique query strings that will allow you to filter a report page.
URL links ca also be embedded within a dataset such that a published report can expose links to other reports with a pre-defined filter condition.
How can you do it? Find out in the steps below:
1) Identify the column you will be using for the URL filtering. In my case, I used a COVID Dataset and I chose the Country column.
2) We need to get the URL of our report, so once you're ready publish your report to Power BI Service!
3) Get the URL of your published report, it will look similar to this:
4) Now, go back to Power BI Desktop. We need to create a new column in our COVID table that will allow us to make our report URL dynamic. Filters can be added to the query string of a report URL using the following syntax:
?filter=<Table>/<Field> eq 'value'
As we want to add Country as filter in my URL, it will look like this:
...powerbi.com/groups/eb6da2a4-123c-48e7-8807-7af2dba73923/reports/56b80097-1d4a-4037-94b9-a5ded0936c88/ReportSection?filter=covid_data/Country eq 'Australia'
5) Go to the Query Editor, and add a new column to merge your report URL and the column you want to use as filter:
My new Dynamic URL column:
Dynamic URL = "https://msit.powerbi.com/groups/eb6da2a4-123c-48e7-8807-7af2dba73923/reports/56b80097-1d4a-4037-94b9-a5ded0936c88/ReportSection" & "?filter=covid_data/Country eq " & "'" & [Country] & "'"
Note: Remember that table and field names in URL parameters can't have any spaces!
6) Click Close and Apply :
7) On the Data View, select the new column and set the Data Category to Web URL:
8) Add the new Dynamic URL field to the table visual you have created. Next, go to the Format pane and enable the URL icon setting under Values:
9) Now let's test this!
Publish your report and to test your new dynamic URL link just click on it to see what happens!
+ GO THE EXTRA MILE +
In my example my Dynamic URL is pointing to the same report page. If you want, you can use the URL of another report page, or you can even use the URL of a different report altogether!
Dashboards with dynamic URLs
If you pin the table visual that contains the dynamic URLs to a dashboard, you can still use the URL by just clicking on the URL symbol:
You can also set a custom URL on the tile itself, so when you click on it the users will be redirected to a filtered Report Page.
How to do this? Check the video below to find out!
In the example I used a Dynamic URL to filter my report page to show UK COVID data only. This way, whenever a user clicks on the pinned Map Visual in my dashboard, they will see the report for UK data only:
The dynamic URL I used as the same format as the ones I did in the beginning of the post:
...powerbi.com/groups/eb6da2a4-123c-48e7-8807-7af2dba73923/reports/56b80097-1d4a-4037-94b9-a5ded0936c88/ReportSection ?filter=covid_data/Country eq 'United Kingdom'
You can also copy the link from Power BI Service. In this case, it will show like this:
That's all for today! 😁