Re-building a Power BI report

Wednesday, April 15, 2020

There are moments like this when the world seems to have stopped turning yet time keeps ticking away. At first you enjoy the peace and quiet, the long awaited rest and the limitless episodes on Netflix. However, if you have reached the point where dreams are on pause and the sound of birds chirping is starting to make you nervous, then perhaps the time has come to learn something new. 
In this blog, I will try to take your mind off of the birds and confining spaces and help you discover a new part of the Power BI world. I’ll take you along on a quest to see if ‘X’ marks the spot to treasures untold. Starting point is an old Power BI report that we’ll rebuild to be fancier and more performant. If you haven’t built a Power BI report yet, don’t worry! Some parts of the journey may feel like you’re drowning in the vast ocean, but they may come in handy once you’ve discovered the Power BI kingdom on your own time and pace. The original report was based on an aggregate table. It was a cross join of sales information and different measure types (MAT, YTD, …).

First task on our treasure quest is to fly across the ocean to the Island of Power BI and perform the cross join in the report itself instead of the aggregate table. 

The Island of Power BI

The base table for the new report is the original sales fact. Together with a few dimensions we’ll need, we also add a table that holds the different measure types we want to use. Unlike the dimensions in our data-model these measures will be stored in disconnected tables.

In our case the report holds multiple ‘on-screen’ filters. The first are the measure types that decide whether we want to see the data as YTD, MAT, etc. The sales type filter decides what type of sales records we want to see as well as which measure types can be selected, hence the extra table that is linked to both.  

Once all the data is loaded and the correct relationships are created, I start by creating a measure to calculate the turnover based on the chosen sales type. 

I then do the same for the measure types, based on the previously created measure. 

Now we can use our disconnected tables to create ‘on-screen’ filters.

And just like that, we’ve conquered the first obstacle on our quest.

The Bookmarks-forest

One of the villages in the Power Platform kingdom told of a legend, saying you could create tabs in a report using bookmarks, let’s see if we can find what we need in the Bookmarks-forest to do just that. 

First I create a new page with some visuals and add a new bookmark of that page. On my main report page, I create a ‘tab’ button and add the action of following the new bookmark, upon clicking it. I repeat that for each ‘tab’ I wish to create. I hide each underlying page so that the only way to navigate to them is by clicking on the tabs. You can use the same process if you want to switch between graphs within the same report space. In this case there is one extra step: hiding the other underlying visuals (see the figure below). Make the selection pane visible by selecting it in the ribbon. Here you can decide for each bookmark which graphs and other visuals you want to see and which ones you want to hide, by clicking on the eye-icon. 

The effect of the bookmark tabs and buttons is illustrated in the figure below. Part 1 shows that clicking on the tab “Articles” (A) results in visualizing the articles page (B). When clicking on the button “Supplier” (see part 2 - (C)) the graph shown, is switched from “brand” to “supplier” (D), within the same page.  

The tooltip dessert

Before we get to where ‘X’ marks the spot, we need to cross the tooltip dessert. A tooltip within Power BI can show extra information about a data-point in your visual.

Which is very helpful, but we’re going to use a bit more magic and show a mini-report within the tooltip. First you need to enable the ‘tooltip’ function in your main report page. If you want all your filters to apply to your tooltip then enable the ‘Keep all filters’ option, if not, drag all the fields you want to use to filter the tooltip in the underlying window. Once this is done, create a new report-page and set its size to ‘Tooltip’. Now you can add whatever visual(s) you want to show in your tooltip. When you have completed this, go back to your main report page and select the visual for which you want to show the tooltip. Go to the visuals settings, select tooltip and choose report-page as the type and the new report page where you made the tooltip as ‘page’. 

Eh voila, magic! 

‘X’ marks the spot

The reason I started this little project is because the report that I had build previously was getting slow and after learning some more about Power BI, I found it a bit dull. I learned all these new functionalities and I thought to myself, I can do so much better!

This new report is quit a bit faster (not yet as fast as I would like) and it’s gone up a level on the ‘fancy’ ladder. Performance wise I can still improve some visuals and functionalities, especially once published to the Power BI service, but in all I can conclude that it might not be an abundance of gold or coins, but it’s my own little treasure to hold.