Easy As Insights — Part 2: Improving Power BI User Experience — Dynamic Filtering Using URL Parameters

In this series of “Easy As Insights” articles I will cover some of the best practices for improving user experience of Power BI insights reports users, especially if you are in the corporate world where your team is developing insights for many departments and users across the organization.

In this article I will explain how to improve user experience by dynamically filtering data using url parameters.

User Requirement

Power BI has ability to use Role Based Security to filter data based on the user who is accessing insights.

But many a times I have come across business requirement where when a user click on a Power BI report, the report should open with data filtered based on the user’s role but also the user should be able to see all the relevant data.

Let me explain the requirement with an example. In the example screen, when a user who is managing products for Product Category Bikes, the report should open with data filtered for Bikes. But when the user clicks on the reset button data should be displayed for all Product Categories.

In such use cases Role Based Security cannot be used as data will be always filtered by the user name and cannot be changed to display all data.

Solution — Dynamic filtering using query string parameters in the URL

In Power BI service there are few ways of achieving the above requirement.

1. Bookmark the report with required filters/slicers applied
2. Dynamic filtering using query string parameters in the URL
3. Drill Through to another report

In this article we will focus on Dynamic filtering using query string parameters in the URL. The major advantage of this technique is this can be used in Power BI Service and in-house Power BI Report Server (PBIRS).

In-house Power BI Report Server (PBIRS) does not have the capability to bookmark reports. This is one of the major drawback in PBIRS in terms of user experience. But many organizations still prefer in-house solutions and PBIRS is the only option.

To meet the above use requirement explained in PBIRS:

1. Develop the Power BI report without any data filtering (as shown in the sample screen)
2. Add a Reset button and link it to default state Bookmark
3. Publish the Power BI report to the in-house PBIRS
4. Get the URL link to to the report

Since we are focusing on in-house PBIRS solution, we need to decide on the strategy to centralize the links to reports. Usually organizations will have an intranet portal where users can access content relevant to them. This could be a custom developed portal, Office 365 SharePoint portal or some organizations may prefer to use Microsoft Teams as the central place for departments.

Whatever the portal strategy of the organization is, it is the place where users will go to access the URL link to the Power BI reports.

Excerpt from Microsoft:

In the above sample screen, say the URL for the report is:

http://MyPortal/Salesreport

Now we can filter the report for Product Category = Bike by adding the parameters to the URL.

http://MyPortal/Salesreport?filter=ProductCategory/CategoryName eq ‘Bike’

Note: If you are including the URL in the HREF tag in a HTML file you will need to escape the single quotes and encode the URL.

HREF encoded version of URL:

HREF = “http://MyPortal/Salesreport?filter=ProductCategory%2FCategoryName%20eq%20&#39Bike&#39”

When a user clicks on the above URL the report will be filtered by Product Category “Bike”. When the user clicks on the Reset button on the report then data will be reverted to all Product Categories.

If you want to open the above URL in browser full screen mode then use the below url:

HREF = “http://MyPortal/Salesreport?filter=ProductCategory%2FCategoryName%20eq%20&#39Bike&#39&rs:Command=Render&rc:Toolbar=false”

As you can see for in-house PBIRS based Power BI reports which lacks bookmark capability, dynamic filtering using url parameters is the best solution to achieve role based filtering with capability to revert to no role based filtering.

Easy As Insights Series of Articles

Easy As Insights — Part 1: Taming Power BI Maintenance — Insights Template
Easy As Insights — Part 2: Taming Power BI Maintenance — Visuals Naming Conventions
Easy As Insights — Part 3: Taming Power BI Maintenance — Bookmarks Naming Conventions
Easy As Insights — Part 5: Improving Power BI User Experience — Using Navigation Pages

Easy As Insights — Part 6: Improving Power BI User Experience — Dynamic Filtering Using URL Parameters