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 Interview questions: a different way to know your applicants

Power BI Interview questions: a different way to know your applicants

One day you will have to recruit or will be involved in a recruitment process for a Power BI position. Nobody knows everything about a tool or a subject, that's also why our job is fun, we never stop to learn.
Asking very precise questions aren't always the best solution, I think it's also nice to ask "open" questions. It's a way to learn from your applicant and to understand how they will deal with their daily job.
I tried to write a list of questions I would like to ask (and maybe, yes, to answer). 

Questions list

  • What are the different languages used in Power BI? Can you explain their usage?
  • Do you know one data modeling concept? If yes, can you explain it in your words?
  • We have to transform our data, where and how can we do it?
  • What’s the difference between these two visuals: Table and Matrix
  • By dragging and dropping a field in the Values area we can create implicit measures. Could you give me a shortlist of different aggregation functions available?
  • We want to implement security in our data. Do you know a way to hide some data to a group of users?
  • When we select a table in Power BI desktop, we have an option called “Mark as date table”. Do you know why?
  • Users can filter data in multiple ways! Can you give me some examples?
  • Do you know what Power BI Datasets are, and why would you use them?
  • Power BI community gives us a lot of different External tools, do you know some of them?
  • I’m the IT manager and I never heard about Power BI before. Can you explain to me what it does and what’s the gain to use it?
  • If you know DAX, can you give me 3 functions that you use the most?
  • We are wondering if we need to buy Power BI Premium, do you know which functionalities are available in Premium?
  • When you need to build a report, where do you start your journey?
  • Which points are the most important to not fail a Power BI project?
  • You start a new project, your first mission is to reduce the size of the dataset. Which are the different operations you will perform?

It exists already a ton of different lists of questions available on the internet, I wanted to break the rule with a different kind of question.

If you want to add more questions to the list, feel free to send me a message.

LazyDAX V2: Learn DAX from examples

LazyDAX V2: Learn DAX from examples

Introduction

Learn from examples.

LazyDAX helps you to understand DAX with concretes and simple examples. You can change the existing measures or create yours.
LazyDAX is also an easy dataset for your tests and presentations.

Why LazyDAX?

 

In my DAX journey, I met two kinds of peoples.

Business Analyst (sometimes also Excel Users) who had very good abilities to learn DAX.

And people like me, with a slow learning curve with DAX. (Unfortunately, I’m not so bad with SQL, C#, and a ton of different languages. Yes I’m a developer :))

During our DAX learning, Mathieu Ricour and I built the first version of Lazy DAX. It was a place for us to keep and save our code around a simple and concrete example.

After many months, I’m still using this file on a quasi daily basis. And I’m convinced, it can also help you.

In this second version, I’m sharing with you a list of useful measures and a reusable dataset.

Download it on GitHub

https://github.com/arnaudgastelblum/LazyDAX/raw/master/LazyDAX.pbix

 

More measures and descriptions are coming soon.

 

 

Quick overview:

 

PIT Point In Time

PIT Point In Time

Introduction

We often have descriptive data in our Power BI models. Data may vary over time and have validity dates. It can become a real headache when creating a data model.

What kind of data?

  • A car could change his production plant through time.
  • A car can have different engines over time
  • A customer can have through time:
    • One or more loyalty cards
    • One or more addresses

What data types?

  • Multiple data
  • With a notion of time. (The information is valid from one date to another and potentially followed or overlapped by another)

PIT – Point In Time – Datavault

Point In Time is a concept widely used when creating a data warehouse in a Datavault flavor. Performing a snapshot and historizing the data makes restitution much easier.

(I will not discuss the pros or cons of Datavault, but I have my preferences ^^)

If you want to know more, I invite you to read this article. (Written by Dan Linstedt)

https://danlinstedt.com/allposts/datavaultcat/pit-bridge-value/

Demo

What do we expect?

It is exciting for our users to navigate through the data by applying a time filter (CFR Slicer Date)

How: By creating a DAX measure. (#PIT Shoes)

The example above shows dynamically Pauline’s shoes.

DataModel

Our data model

The following example is quite simple (The Power BI file is available for download at the end of this article)

Many of you know it, I am passionate about Power BI, running, but many ignore my “passion” with running shoes. (Nobody is perfect !)

The example refers to a list of runners who have one or more pairs of shoes over time.

The Runners table:

The Shoes table

Model specificities

  • RunnerId (The Runner Table Key) is available for all shoes.
  • For performance reasons, the date stamps are duplicated and converted into numeric.
    • ValidFrom – ValidFromInt
    • ValidTo – ValidToInt

More information

This is denormalization of a star schema generally composed of a Bridge and a dimension table.

In a Datawarehouse (Kimball), we should have a Bridge: BridgeRunnerShoes and a dimension: DimShoes

Why?

It helps our users to have better comprehension. (Simplicity)
The Vertipaq engine will compress the redundant information
To avoids an extra join / relationship.

You want to know more about Bridges:

 

https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/multivalued-dimension-bridge-table/

A more complete model could look like this one

TimeLine

Graphically analyze the different interactions.

  1. Our user pick a date via the PIT Date slicer
  2. The shoe table changes dynamically and display the one owned on that date.

NB The blue bar drawn on the timeline has been added manually in MS Paint. (If you know a visual component that can display it, do not hesitate to contact me)

Filter: 01/02/2019 | Pauline

Filter: 01/03/2019 | Pauline

Filter: 01/05/2019 | Pauline

Filter: 10/10/2019 | Pauline

Filter: 01/12/2019 | Pauline

Implementation

Create a Calendar table

We have to create a Date table, which will not be associated with our model. (Called here PIT Calendar)

To quickly create a Calendar table, I suggest you write an M Query in the Query Builder. The following articles will give you the approach.

Generating A Date Dimension Table In Power Query

https://www.mattmasson.com/2014/02/creating-a-date-dimension-with-a-power-query-script/

Create a DAX Measure

This measure Filter the Shoes table by the selected date.

#PIT Shoes =
VAR CurrentDateInt = MAX(PIT_Calendar[DateInt])
RETURN
CALCULATE
(
DISTINCTCOUNT(Shoes[ShoesId]);
FILTER('Shoes';
Shoes[ValidFromInt] <= CurrentDateInt && Shoes[ValidToInt] >= CurrentDateInt
)
; 'Runners'
)

Understand this DAX expression

VAR CurrentDateInt = MAX(PIT_Calendar[DateInt])
or

VAR CurrentDateInt =
IF(
ISCROSSFILTERED(PIT_Calendar)
;MAX(PIT_Calendar[DateInt])
;VALUE(YEAR(TODAY())*10000 + MONTH(TODAY())*100 + DAY(TODAY()))
)
A variant of the previous expression can test if our user specified a PIT Calendar date and apply the current date if not.

Why?

  • The user wants to have the situation of the day (without having to modify the PIT slicer at each opening of the report)
  • The user forgot to create his report with the PIT slicer. Rather than propose the situation in the future, we apply the current one.
FILTER('Shoes';
Shoes[ValidFromInt] <= CurrentDateInt && Shoes[ValidToInt] >= CurrentDateInt
)
The variable contains the date selected by the user and is dynamically applied to our dataset. (Shoes)

Conclusion

Now, you know how to make your data alive.

Feel free to comment if you have more ideas, improvements, tips, or comments.

Download the Power BI example file here:

https://github.com/arnaudgastelblum/LazySnail/raw/master/LazySnail_PointInTime.pbix 

 

Edit 02/12/2021: If you want to know more or enjoy another point of view Reza Rad did a nice article on this topic too:

Power BI From and To Date Filtering with One Slicer – RADACAD

Pie Chart is bad!

Pie Chart is bad!

      During my presentation at Power Saturday in Paris, I wanted to demonstrate to an already very optimistic public that Power BI is a great tool and that modeling is a must-have for their reports. (Data Modelling is not the subject here)
Power BI a super tool? Yes, and to complete this idea, I added some slides to highlight each piece of the tool.

One of the slides showed the ease and elegance of the visuals.
After a quick search on Google images, I copied a screenshot created by a user in Power BI and added it to my presentation.

In the audience, two friends looked at me with a horrible face. The damage was done, I had slipped the image of a Pie Chart!

Nothing crazy, isn’t it? But when you know a bit of the history of the Pie Chart, we realize that this visual component is not recommended in the world of Data Viz. This blog post also follows some more or less tense discussions between colleagues.

I wrote this article for many reasons:

  • This point is already super documented on internet, but some people never read them before. (I provide links at the end)
  • I wanted to convinced myself first, and created a Power BI file to confirm this point. (CFR the following screenshot)

I was a Pie Chart lover

I know that its use is not recommended and I try not to use it.
But yes, I liked it!
• It’s beautiful!
• It’s colorful!
It gives a professional look to our reports!
• It shows information!
It gives me some pride to not use tables or matrix. (Yes, I like numbers, not you?)

I think we are used to seeing this kind of visualization since our earliest childhood in commercial brochures, professional documents.

The Pie Chart is visual, and help us to return informations quickly. But unfortunately, this information is not always the right one.

Why?

To compare groups, we evaluate the difference of the amplitude of the angles while a histogram will request us to compare lengths between them.

A long description is not needed, I would prefer to show you 2 set of data, both represented in a Pie Chart and histogram.

Your first look will give you some information, but not as much precise compared to the two histograms below. It’s possible to have quick information, but very hard to have a clear interpretation of them.

If I didn’t convince you, try to sort each portion from the smallest to the biggest. Try to do the same with the histogram and compare the time elapsed for both.