Tag Archives: URL

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.

Power BI Filters in Action

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.

Netflix Originals to be Analysed using Power BI URL Filters

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.

https://app.powerbi.com/groups/e4b1a177-e65c-4193-8742-cda70a21ce88/reports/787a839b-dec4-4e26-a063-be49fcf18879/ReportSectionc33aa21606003eda9841

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.

How to get a Power BI Embed Link

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.

https://app.powerbi.com/reportEmbed?reportId=787a839b-dec4-4e26-a063-be49fcf18879&autoAuth=true&filterPanelEnabled=False&pageName=ReportSectionc33aa21606003eda9841

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

Power BI Editing a Sharepoint Site for Power BI URL Filters
Editing the site

Hover over so that you can add an element

Power BI Embedding in Sharepoint or Power BI URL Filters
Adding an element

Select to add an embed

Adding a Sharepoint Embed Element for Power BI URL Filters
Adding a Sharepoint Embed Element

Then you can add your Power BI Embed URL from above

Adding your Power BI Embedded URL
Adding your Power BI Embedded URL

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 🙂

How to embed Power BI inside Power Apps

Filter Operators for creating complex URLs

Understanding the operators is a key part of understandiing how to create complex URLs

operatordefinitionstringnumberDateExample
andandyesyesyesproduct/price le 200 and price gt 3.5
eqequalsyesyesyesAddress/City eq ‘Redmond’
nenot equalyesyesyesAddress/City ne ‘London’
gegreater than or equalnoyesyesproduct/price ge 10
gtgreater thannoyesyesproduct/price gt 20

Table/Date gt 2019-05-20
leless than or equalnoyesyesproduct/price le 100

Table/Date lt 2019-05-20
ltless thannoyesyesproduct/price lt 20
in**includingyesyesyesStudent/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.

IdentifierUnicodeCoding for Power BI
Table NameSpace is 0x20Table_x0020_Name
Column@Number@ is 0x40Column_x0040_Number
[Column][ is 0x005B ] is 0x005Dx005B_Column_x005D
Column+Plus+ is 0x2BColumn_x002B_Plus
taken directly from Microsoft Docs
6 Simple Steps to building successful business Power Apps
How to build business power apps
The UP Podcast Joins the Power Platform Hall of Fame
Find out More about the Microsoft Up Podcast
1999 students building awesome apps
Today is a day for Pride. I feel proud today as the …
Rebuilding the 3 Screen Power App with ease from Blank in 45 minutes
You can build a 3 Screen Power App in about 3 minutes …