Filter a Power BI report passing parameters through URL

Filter a Power BI report passing parameters through URL

Power BI offers us a very sympathetic way to bring a bit more flexibility into our Reports. It’s not a brand-new feature, but I’m not sure that you know it!

You can display a report and automatically have a page filtered by the parameters you define in the URL.

Why is it cool?

But

  • It’s indeed not a way to add security to your reports!! (Filter can be removed by your users) => The Row-level security is what you need.
  • It’s not possible to use it in an iframe with a report “published to web”

First, Microsoft wrote already a nice article on this topic. I suggest you read it for more information.
https://docs.microsoft.com/en-us/power-bi/collaborate-share/service-url-filters

How to

  • Open your report in Power BI Service and go to the page you want to filter
  • Click on the address bar and at the end of this line, add the following code ?filter=
This is the example used in this article. (You can also download this Power BI on this website. Search LazyDAX on the main page)

Filter on numeric value

?filter=Calendar/Year eq 2022

The first part of this expression refers to the dimension (Table) -> Calendar
The second part refers to the attribute name in this dimension -> Year
eq means equals

Filter multiple numeric values

?filter=Calendar/Year in (2022, 2023)

Filter on a text value

?filter=Product/ProductCategory eq 'Fruit'

Filter multiple Text values

Add Multiple filter on differents attributes

?filter=Customer/Country in ('Belgium', 'Nederland') and Product/ProductCategory eq 'Fruit' and Calendar/Year ge 2021

More operators are available

Source: Microsoft website (see url below)

Like mentioned at the beginning, everything is already well explained on the Microsoft website: https://docs.microsoft.com/en-us/power-bi/collaborate-share/service-url-filters

LazyDAX: a Power BI File for your presentation, discoveries, or learn DAX

LazyDAX: a Power BI File for your presentation, discoveries, or learn DAX

If you want to:

  • Present interesting Power BI discoveries to your colleagues
  • Test your DAX Measures, reporting on a very clean Star schema
  • Discover multiple DAX Functions
  • Show different Power BI features by examples

Then this Lazy DAX file is for you! It’s a Power BI Desktop file that you can freely download on my GitHub repository. Indeed you can change it, publish it and do whatever you want with it.
This Power BI contains data and is designed in a Star schema model. You don’t need to connect on a server and can change the data, because I used the “Enter Data” method.
DAX: I organized multiple Measure in the same way Microsoft present all the DAX functions in their folders. It was an efficient way for me to test it all.
Visuals: On different pages, I used all the existing visuals, and tried to provide you some ideas to build a more fancy report.

I spent a lot of my free time on it, and in this new version I changed the look n’feel and try a dark mode report. (Yes, it’s also a playground for me to test Power BI)
I created it more than 3 years ago now, but I still use it every week. (If not every day)
I can use it for my presentation, and help people with DAX. I hope it can help you too, and would really enjoy hearing your thoughts, ideas and requests.

To download it, follow the blue button or click on this link: https://github.com/arnaudgastelblum/LazyDAX/raw/master/LazyDAX.pbix

You can also view all different pages in this PDF file

Power BI: Cheat Sheet Links

Power BI: Cheat Sheet Links

I’m listing here all the links and information who can be useful for us 🙂

Design / Layout

Misc

Copy Power BI Model from server (with tabular editor) and rebuild a fresh Power BI File: https://www.thinkbi.de/2022/05/03/copy-power-bi-data-model-back-to-pbix-file/

External tools

Power BI Business Ops [Install them all]https://powerbi.tips/product/business-ops/
DAX Studiohttps://daxstudio.org/downloads/
Tabular Editor [Free]https://www.sqlbi.com/tools/tabular-editor/
Tabular Editor 3https://tabulareditor.com/
Side toolshttps://github.com/didierterrien/Power-BI-Sidetools

Roles in Power BI

https://docs.microsoft.com/en-us/power-bi/collaborate-share/service-roles-new-workspaces

https://www.mssqltips.com/sqlservertip/6487/power-bi-workspace-permissions-and-roles/

Visual Studio Code

Key shortcut

Show opened windowCTRL + K | CTRL + P
Search a file (based on his name)CTRL + P
Search a file (based on content)CTRL + F
Multi cursorCTRL + SHIFT + ALT

Useful plugins

  • Line Note
  • DAX for Power BI
  • Bookmarks
  • Focus
  • Git Lens
  • Theme: One Dark Pro
  • Power Query / M Language
  • TODO Highlight
  • Writer mode
  • Material icon Theme

Markdown

Power BI Visuals List: Which Graphic to choose?

Power BI Visuals List: Which Graphic to choose?

Click on the image to show an example for each graphics available in Power BI

You can also download the Lazy DAX file Here (Where all examples are available)

Why this list?

Today, I had some time to learn!
I think I’m not that bad for data modeling, T-SQL, SQL server performance, Power Query, DAX, coaching and understanding user requirements. (Yes, sometimes it’s very good to be kind to yourself 🙂 )

But putting effort into many different techs skills, I should mention it to the whole world (at least you, because you are reading my blog post).

I’m not that good when it’s time to pick the best visual to show our data!

Table and Matrix fan

sci-fi GIF
Me and the evaluation context

You will maybe recognize you in my description! I’m the kind of guy who uses Table and Matrix a lot. For testing and understanding my data!
Matrix is wonderful! You can look at the behavior of your measures at the different grain in one minute! List, Sub Total, Total!! Amazing!

I can’t stop my crazy pleasure to click on all that kind of visuals

When I’m proud of it, I try to build a visual useful for my users. So I transform this table into a graphic. I click the first button, then I change for the second and I can’t stop my crazy pleasure to click on all that kind of visuals!!
But do I know what I’m choosing? NO!
I knew, yes.. But right now, I’m just clicking to find the best one who will give me the satisfaction between 2 tasty coffees. (And yes, Power BI is also a question of feeling and pleasure. Like a very addictive game)

With the image on top, I tested all these graphics and keep a trace of them. First, to help me the next time I need to pick the right one. (And I hope, it can help you too)

Visualization types in Power BI – Power BI | Microsoft Docs

Next step? I still need to learn more about visualization! And I will!
And you, do you click everywhere until you have the best one? If no, how did you become data artist / story teller?

Power BI: Duplicate of Duplicate – The best way to test your reports and measures

Power BI: Duplicate of Duplicate – The best way to test your reports and measures

This article seems obvious, but that’s something that I do a lot, and this is the first thing I do when somebody asks me a question.

After many weeks, some clients tell me: “Now, I’m using your technique too”

But I don’t feel to own this! It’s pretty simple, but in fact, if you didn’t use it before, it can help you a lot!

So, yes, I’m duplicating my page!

A lot!!!!

A focus version:

When I have an issue or a question, the first thing I do (after crying), it’s duplicate the page.

On this page, I remove everything not useful to test

  • Visuals
  • Images

I transform all the visuals I want to test (hopefully only one) into a table.

Let’s call it the “focus version

Test your filter context:

When I have a cleaner version of my report I can duplicate it. (again)

Introduction To Textures in Direct3D 11 (Windows) | Data design, Texture,  Visualisation

On this new page, I remove one by one:

  • Filter on slicers
  • Filter in the filter pane (Be careful to remove the one hidden)
  • Hidden Slicers (Synced slicers)

Until my values change and I can understand them.

This step helps me to trust the data and understand the measure. It also helps me to trust the behavior of each filters.

Test one particular case:

You are the best and main debugger of Power BI!

debug Icon - Download debug Icon 83827 | Noun Project

Based on the previous topic, now you are confident with your evaluation context! I would recommend you to filter on a very specific case available in your data.

For example: 

  • One transaction ID (one row in your sales table)
  • One Invoice Id

Many users don’t look their data at a very low level, and trust me, that’s very important!

If you know the story and behavior of your DAX measure for a very specific case, you can predict how it will work for 2 and many more.

Split your measures and display their result

In our DAX measure, we can have multiple steps and complexity. That’s why the best practice is to use Variables (VAR), and I know that you already use it!

Metal Cover VGA Touch Screen Monitor voor Industriële PC. Mini pc monitor  MINI itx Display|touch screen monitor|vga touch screentouch screen -  AliExpress

But do you test it? How to do it?

  • Duplicate your measure (yes, Duplicate duplicate everywhere) and change the RETURN section by returning the value of one of your variable.

Many month after our work some changes can impact our measure! 

  • Relation between tables can be different
  • Data aren’t the same again
  • You change the slicer with a different attribute! (Same value, but different attribute, ..)

By testing your old good variables, you can trust and confirm how they work. (Specially if everything is still ok for your evaluation context)

Conclusion:

Most of the time, on a report, crowded with many visuals we aren’t able to find the main reason for our “problem”.

We keep focus on not useful stuff and we are to scare (or lazy) to have a look deep dive.

In two sentences:

  1. Less is more
  2. Duplicate your page et destroy everything

Bonus: Duplicate page and measure is one point! But don’t forget to duplicate your .PBIX file.. In case of emergency 🙂

I wish you all the best and a lot of duplication.