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.
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
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.
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
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)
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?
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)
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!
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!
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:
Less is more
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 🙂
