Category Archives: Power BI

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
An Introduction to ChatGPT
ChatGPT is coming to a browser near you, and I've been so …
Step by step guide to creating Power Platform Solution Aware Power BI reports using Dataverse
Microsoft have recently introduced the ability to create Solution Aware Power BI …
Launching Power Apps in Full Screen 4 Simple Steps
How to LAUNCH POWER APPS IN FULL SCREEN
6 Simple Steps to building successful business Power Apps
How to build business power apps

Scottish Summit 2021 – Getting the most out of it

Scottish Summit is just around the corner. But how do you get the most out of the 365 sessions that will be on? Here’s a little guide.

Signing up for the event
Signing up to the Scottish Summit

From the home page at https://scottishsummit.com/ you need to head to “Watch Live”

Scottish Summit Home Page

You’ll then be prompted to choose a password if you’ve not done so already. You’re now in the hub of the event.

Signing up for a session
  • Head to the Schedule
  • Go to the Gallery view
  • Select the date you are interested in
  • Hit the filter buttons
  • DESELECT Show All
  • Select the track you are interested in – I chose Power Apps
  • Select the session
  • Add to your schedule

I’m presenting with Pieter Veenstra at 11:00 am in Prestwick on PowerApps4Kids.com Lessons Learned from Teaching Children. A direct link to the session can be found here https://eventmobi.com/ss2021/agenda/021f9132-738f-4b5e-b7f2-8ad6d993abb6/ce3a59fd-9b11-4698-acc5-75d3ec409b3b

If you would like to learn more about Power Apps – why not head to the Free courses at www.powerplatformlearn.academy – we have courses for children and for adults. Hope to see you there some time.

App Download

Android App

https://play.google.com/store/apps/details?id=com.community.emss2021&hl=en

IOS App

https://apps.apple.com/gb/app/scottish-summit-2021/id1526867435

Power BI Buttons with Pop ✨ – Aka Unicode and Unichar

Did you know that the human brain processes images up to 60,000 times quicker than text? I haven’t been using Power BI for a little, but it occured to me that it would be nice to have Buttons that were a little more visually appealing. Maybe the πŸ“Œor πŸ“† icons could come in handy? This post shows you how simply you can do this using unicode.

Whilst the above statistic can be disputed it is undoubtedly the case that it’s quicker and the investement of companies into logos of all shapes and sizes suggests that there is an economic value to logos. Check out some stats here

Best Brand Logos Images With Names Collections | Logo images, Logo ...

Unicode provides us with a massive catalogue of 1600 or so images that we can use in our Power BI reports AND our apps. So let’s consider the steps required for them to work in Power BI and apply it to a button

  1. Create a Measure
  2. Add the following to the measure
Txt Filters Navigation = "Filters "&Unichar("128505") --This is a checkbox

3. Create a button
4. Go to the text properties of the button

5. Hit the fx
6. Select the measure you just created

7. Save and close
8. Go to the Action property of the visual, Select Page Navigation and Select the page required
8. That’s it – you’re done!


The image portfolio is very significant and well worth a review. I have catalogued them at the end of this post and down below is another example, this time showing a colour version


Good luckπŸ˜ƒ

Office 365 and Power Platform Learning Links

Microsoft Power Platform

Here are a series of links that will help you to be successful with the platform.

Getting Social

Alternative Blogs

Techniques for better Apps

In a Day Series

Getting to your admin sections