Power BI URL Filters made easy
Did you know that you can add Power BI URL filters into a web address to take you to a precise page, filtered in the way that you require? It is a little tricky, but definitely worth considering if you have a data story to tell.
It really becomes something compelling when we use what we have learned and combine it with some Power Apps magic, and then the possibilities really open up for us.
Table of contents
- The tale of Leicester City Football Club
- Downloadabale content to help you write your own Power BI URL Filters
- Power BI Base URL – Standard
- Adding a pageName to our base embedded url
- Filtering your Power BI Report
- Embedding Power BI Inside Power Apps
- Filter Operators for creating complex URLs
The tale of Leicester City Football Club
Down below we have the story of how Leicester City avoided relegation and went on to win the Premier League in the following season. In this case I’ve created a Power App to tell my story.
Content to help you write your own Power BI URL Filters
If you would like to play along with this post you’ll need to download the following file, and then upload it to the Power BI service. It’s a simple Power BI report which looks at various metrics associated with Netflix Originals productions.
The data was taken from kaggle and can be found here.
The Basics of Power BI URLs
Let’s start at the the most basic level and consider the “base urls” – these are the urls that come before we add all the additional filters.
Power BI Base URL – Standard
A typical URL looks as follows :
Lets unpack this a little
groups = the workspaceid for the location of the report. This will be 36 characters long
reports = the id of the report in question. Again – 36 characters long.
ReportSection = the id of the page that you are trying to access. Every Power BI report has 1 page called ReportSection – the first page, but every other page will look a bit more like the following ReportSectionc33aa21606003eda9841 these points become important later on as we use these principles when creating embedded URLs, we just switch things around a little bit.
Standard URL example. We could send this to a colleague.
Power BI Base URL – Embedded
Base URLs for Embedded reports are effectively the same, with a few additional tweaks. The reason we use these is because they allow us to surface the report elsewhere.
The easiest way of getting to an embed link is to go to File->Embed Report->Website or Porta.
Once you’ve done this you’ll get a very long link. Delete EVERYTHING after AutoAuth=true
It will look something like this
https://app.powerbi.com/reportEmbed?reportId=787a839b-dec4-4e26-a063-be49fcf18879&autoAuth=true
Embedded example
Adding a pageName to our base embedded url
The pageName is effectively the name of the tab on which you would like the user to land. If you do not specify this you will land on the page that the author last saved the report on.
In this case the page we are heading to is ReportSectionc33aa21606003eda9841 (our landing page)
Using our Embedded URL in SharePoint
We can use our embed url this in many ways. One good one is using SharePoint.
To so you simply need to go to your site where you are a site owner and click to edit the site
Hover over so that you can add an element
Select to add an embed
Then you can add your Power BI Embed URL from above
Filtering you Power BI URL
The following won’t work within Sharepoint as it is not permitted within the service. It will however work in Power Apps.
The act of adding Power BI URL Filters is relatively straightforward. Anyting after the ? in the URL and we can add &$filter=YourTableName/YourField to begin to apply filters this is CASE SENSITIVE and if you don’t observe this your filter won’t work.
An example of this is as follows
&$filter=Netflix/Genre in (‘Documentary’,’Drama’)
Quite often if you are in the Power BI service you can edit your app and you’ll be able to see all your tables and fields.
The full URL in this case is as follows
https://app.powerbi.com/reportEmbed?reportId=787a839b-dec4-4e26-a063-be49fcf18879&autoAuth=true&filterPanelEnabled=False&pageName=ReportSectionc33aa21606003eda9841&$filter=Netflix/Genre in (‘Documentary’,’Drama’)
Embedding Power BI Inside Power Apps
This is is a really great experience and in this case it’s a far better story told in video for. This 7 minutes could change your life 🙂
Filter Operators for creating complex URLs
Understanding the operators is a key part of understandiing how to create complex URLs
| operator | definition | string | number | Date | Example |
|---|---|---|---|---|---|
| and | and | yes | yes | yes | product/price le 200 and price gt 3.5 |
| eq | equals | yes | yes | yes | Address/City eq ‘Redmond’ |
| ne | not equal | yes | yes | yes | Address/City ne ‘London’ |
| ge | greater than or equal | no | yes | yes | product/price ge 10 |
| gt | greater than | no | yes | yes | product/price gt 20 Table/Date gt 2019-05-20 |
| le | less than or equal | no | yes | yes | product/price le 100 Table/Date lt 2019-05-20 |
| lt | less than | no | yes | yes | product/price lt 20 |
| in** | including | yes | yes | yes | Student/Age in (27, 29) Address/City in (‘Redmond’,’London’) |
** When using in, the values to the right of in can be a comma-separated list enclosed in parentheses, or a single expression that returns a collection.
https://docs.microsoft.com/en-us/power-bi/collaborate-share/service-url-filters
Chaining Filters Together
All we need to do to chain them together is to add “and” to each filter that we are trying to apply
https://app.powerbi.com/reportEmbed?reportId=7b7be7f1-7b69-4fdd-948d-d524ec80d0b5&autoAuth=true&pageName=ReportSection&filter=BreedSelector/Breed in (‘Cocker Spaniel’) and PuppyFinder/Town in (‘London’)
The and expression has been added to add more filters
Special characters in table and column names
Special characters and spaces in table and column names require some additional formatting. When your query contains spaces, dashes, or other non-ASCII characters, prefix those special characters with an escape code starting with an underscore and an X (_x), then the four-digit Unicode, then another underscore. If the Unicode is fewer than four characters, you need to pad it with zeroes. Here are some examples.
| Identifier | Unicode | Coding for Power BI |
|---|---|---|
| Table Name | Space is 0x20 | Table_x0020_Name |
| Column@Number | @ is 0x40 | Column_x0040_Number |
| [Column] | [ is 0x005B ] is 0x005D | x005B_Column_x005D |
| Column+Plus | + is 0x2B | Column_x002B_Plus |