Dynamic URL Filtering on Power BI

Posted On September 21, 2017 by Shannon Pereira Posted in

Introduction:

There may arise a situation where we may want to link one report to a more detailed report dynamically in Power BI. Although we can link dashboards to reports and also provide a custom link for a tile in a dashboard, report to report linkage is currently not a feature available in Power BI.
There is a way to achieve this by using URL filtering. Below is a report which shows Card transaction details based on Expense Type. We want to link this report to a more detailed report after clicking on the URL link icons in the table.

You can download the sample CSV file from here http://bit.ly/nitincsv1

After clicking on the URL Link icon a report like this should open up which is filtered by Expenditure Type.

Steps:

The steps to achieve this are mentioned below:

1. We first need to understand how URL filtering works. The syntax for URL filtering is
URL?filter=Table/Field eq ‘value’
• Table and Field names are case sensitive
• Value should be put in single quotes

The Table and Field can be found under Fields in Power BI Desktop

The URL can be found on the web page in Power BI Online. Every page in a report has a unique URL in Power BI. You can find it in the browser address bar of the report.

The URL filter for filtering ExpType in CardDetails for the value ‘Bills’ would be,
https://app.powerbi.com/groups/me/reports/6ea11c00-85ca-4b8e-907a-42979eaadcaa/ReportSection1?filter=CardDetails/ExpType eq ‘Bills’

2. The above is a very static example for filtering ExpType but this can be made much more dynamic by using DAX
Create a new Calculated Colum for CardDetails

3. Enter the following line as the DAX Code:

Link = “https://app.powerbi.com/groups/me/reports/6ea11c00-85ca-4b8e-907a-42979eaadcaa/ReportSection1?filter=CardDetails%252FExpType%20eq%20%27″&CardDetails[ExpType]&”%27”

In the above function we have put the ASCII values for blanks, apostrophes and equal to sign for ensuring that the URL works efficiently

• Blank -> %20
• Equal -> %252
• Apostrophe -> %27

We append the CardDetails[ExpType] in place of value in the URL Syntax. The rest of the URL remains the same. After pressing Enter the column generated will look something like this in a table visualization

4. The String generated in our column needs to be converted into a hyperlink. Click on Link in Fields, Go to Modeling and select Data Category as Web URL

The Link Column should now look like this,

5. We can convert the hyperlink into a more pleasant URL link icon by going to

Visualizations->Format->Values->URL Icon(Turn On)

6. The final Report generated looks like this

7. Clicking on the URL icon will dynamically filter a new detailed report in a new tab in your browser. To confirm whether the report has been filtered or not, we can look at the Filters section in Power BI Online as well as the URL.