Four Business Use Cases for Power BI Bookmarks Part 1: Pagination

Eric Fait   •   02.16.2018
The bookmarking feature in Power BI has been a fan favorite and one that has spurred quite a bit of innovation. In working with clients to develop their reporting strategy, I get asked to implement reports in a variety of outside-the-box methods. In fact, I considered ‘Can we do it this way, instead?’ as an alternate post title. I’ll walk through four of those methods in-depth in respect to the Power BI Bookmarks feature.
Part 1: SSRS Pagination
Part 2: Filter (and Slicer!) Reset
Part 3: Visualization Swap
Part 4: Executive Presentation
This post will cover:
* The Business Use Case
* Enabling Bookmarks in Power BI
* Creating Custom Measures
* Working with the Selection Pane
* Adding Bookmarks

The Use Case

A lot of companies use a reporting system that creates paginated reports; that is reports that consist of many pages of tabular data. Microsoft’s SQL Server Reporting Services is a prime example of a paginated report solution.

When companies begin implementing Power BI as their reporting solution, all of those old paginated reports are most likely converted to a Power BI report. And that is where Power BI fell short. The data table translated nicely, but the pagination did not. Report viewers only had the ability to scroll down the data table until they reached the spot they were looking for. Let’s see how Power BI’s bookmarking feature can be used to appease the report owner that has to have a paginated report.

The Prequels

In order to use many of the features I mention, including Bookmarks, please make sure that you have the most up-to-date version of Power BI Desktop installed.

Because the Bookmark feature is still in preview, you will need to enable it by going to File -> Options and settings -> Options

In the Options menu, click Preview features and select the check mark next to Bookmarks

Power. Unlimited Power!

In this hypothetical, my client is thinking about getting into Lego reselling, specifically Star Wars related sets, and wants to see a report of all of the Star Wars Lego sets ever made. After a quick search, I find that Rebrickable.com has an API that I can use to get just that. Once I bring the data into Power BI via the ‘From Web’ interface (a topic for another day) we have our dataset.

After cleaning up the data a bit, I’m ready to create my report.

I’m just a simple man trying to make my way in the universe.

First up is to add an index to your data. The index needs to start at 1 as we will be using it to create our page number.

From the Query Editor, in the Add Column tab, choose Index Column – From 1

 

Now make a copy of the index column by clicking Duplicate Column in the Add Column tab and rename it Page.

 

This new column will become our Page Number indicator for our data. Select the copied index column and from the Transform tab, click Standard -> Integer-Divide

 

The window that pops up will ask you what number to divide each of the index values by. The number you enter here will be the number of records displayed per ‘page’ of the report. And finally, select the Page column and click Standard -> Add in the Transform menu. Enter 1 as the value to add to each record in the column.

 

You can also make the records per page a parameter and dynamically get the number of records per page based upon the record count(a post for another day).

*This page number will work great until the moment you filter your dataset in the report itself. So if you need to be able to filter a paginated report, you’re currently out of luck.

I find your lack of faith disturbing.

At this point in the journey, you’re starting to question the reason why you opened up Power BI today. Now we’re getting to the fun part…DAX!

We will need to know:

Current Page Number
Total Number of Pages
Minimum Record Number Displayed
Maximum Record Number Displayed
Total Number of Records

Let’s walk through these one by one.

Current Page Number
This formula is pretty straightforward.
Current Page Number=
SELECTEDVALUE(
YourTableName[Page],0
)

SELECTEDVALUE() will return the value from the column reference as long as it’s the only value available in the filter context. Said differently, if the report is being filtered by Page (All), the measure will return 0 (or whatever you put for the second argument). In our case, we will be filtering by one page at a time.

Total Number of Pages
We need to know the maximum value in the [Page] column which is why we use MAX(). We also need to know that value irrespective of the current filters which is why we use ALL(). Wrapping the CALCULATE() function around those two will give us the desired outcome.
Total Number of Pages =
CALCULATE(
MAX(YourTableName[Page]),
ALL(YourTableName[Page])
)

Minimum Record Number Displayed
Again, straightforward us of MIN() to get the smallest value in the [Index] field. Note that I am not using ALL() so that we are getting the minimum value displayed.
Minimum Record Number Displayed =
MIN(
YourTableName[Index]
)

Maximum Record Number Displayed
Using MAX() to get the largest value in the [Index] field. Note that I am not using ALL() so that we are getting the maximum value displayed.
Maximum Record Number Displayed =
MAX(
YourTableName[Index]
)

Total Number of Records
Here again, as when we got the Total Number of Pages, we need to use ALL() to ignore the current filters and get the maximum value for [Index].
Total Number of Records =
CALCULATE(
MAX(YourTableName[Index]),
ALL(YourTableName[Index])
)

Now that we have our measures created, let’s add two more that will act as a Page Counter and Record Counter.

Page Counter
Here we will just concatenate our Page measures so that we can display them on the report.
Page Counter =
"Page " & [Current Page Number] & " of " & [Total Number of Pages]

Record Counter
Concatenating our Record measures for display.
Record Counter =
"Records " & [Minimum Record Number Displayed] & " to " & [Maximum Record Number Displayed] & " of " & [Total Number of Records]

Never tell me the odds!

Now that we have all of our measures created, lets build the table to display. I gathered the requirements from the client and here is how my table looks.

 

Next, I’m going to add two Card visualizations. One to show the record count([Record Counter]) and the other to show the page count([Page Counter]).

 

If you remember back to when we created [Current Page Number], it uses SELECTEDVALUE and is context sensitive, which is why it says Page 0 of 26. To get everything looking right, add the [Page] field as a Page level filter and set it to 1.

You can see that page count and record count are now displaying correctly, and the vertical slider is gone.

We are finally going to break out the bookmarks! Here is where things get a little crazy. In addition to the bookmarking feature, we will also be using a recently released feature called the Selection Pane. Both of these features need to be turned on via the View tab.

Turning these two features on adds to more wells to the right hand side of the screen, Selection and Bookmarks.

Selection Pane
The Selection Pane shows a list of all objects currently on the visible report tab. Visualizations, Images, Textboxes, and Shapes are the different types of objects that show up here. The eye icon to the right of each object can be clicked to show/hide that particular object.

Let’s make use of the Selection Pane by adding a shape to our report. From the Home tab, click the Shapes button and choose Arrow.

 

Select the Arrow shape, and in the Format Shape well, expand Rotation and change it to 90 degrees. Shrink the arrow to a more usable size and put it somewhere in the lower right hand corner of the report.

You can now see our new ‘Shape’ in the Selection Pane. Let’s give the shape a meaningful name. As you will soon see, naming your shapes/visualizations/images is necessary for clarity. In the Selection Pane, click your Shape and the Format Shape well pops up. This is a bit of a workaround to giving your shapes name, but until the Power BI team allows us to name things directly in the Selection Pane, this will have to do. Expand the Title, turn it on, name it ‘Page 1 Next’, and turn it back off.

 


Bookmark Pane
Now we can add our first bookmark. Click the Add button in the Bookmark Pane. This action takes a snapshot of all the filters, slicers, and selections that are currently set in your entire report. Click the ellipses next to Bookmark 1 and select Rename. Name the bookmark Page 1.

What we have done is created a solid state for Page 1 of our report. Whenever we need to link back to Page 1, all we have to do is reference the Page 1 bookmark.

Make a copy of your Page 1 Next shape by selecting the shape and pressing Ctrl+C to copy and then Ctrl+V to paste. You’ll see the Selection pane now has two Page 1 Next shapes. Rename the copy of the shape by updating the Title to Page 2 Next in the Format Shape well (remember to turn the title back off). Repeat that process one more time, but with this third shape, set the rotation to 270 degrees, name it Page 2 Back, and reposition it to the left of your other shapes. Your report and Selection Pane should look like this:

 

This is a point where the task becomes a bit tedious and for that reason, I’ll cover a five page report. For each page of the data you will need to create two shapes, ‘Page n Next’ and ‘Page n Back’. The last page will only need a ‘Page n Back’ shape. Once you have all of your back and next shapes created, the easiest way to align them all is by selected each type (back or next) in the Selection pane with the Ctrl key.

 

Once selected, use Align menu in the Format tab and align left and top.

 

Do the same for the type of shape.

Now lets add our bookmarks. Create a bookmark for each page of your report by clicking Add in the Bookmarks pane and renaming each bookmark appropriately.

With all of the page bookmarks created, let’s make sure each shape is pointing to the correct bookmark. Click on the Page 1 Next shape. In the Format Shape pane, turn on the Link option, expand it, select the Bookmark type, and choose Page 2.

 

Do the same for each shape, selecting the Page + 1 bookmark for each Next shape, and the Page – 1 for each Back shape. Once complete, click the Eye icon for all of the Page shapes(except for Page 1 Next) in the Selection Pane to hide them. My selection pane and bookmarks pane now look like this:

 

Make sure the page level filter has the Page field set to 1, click the ellipses on the Page 1 bookmark, and choose Update. This action updated the Page 1 bookmark so that all of our back and next arrows are hidden any time the Page 1 bookmark is used. Set the page level filter to Page = 2. Hide the Page 1 Next shape by clicking on the Eye icon, and show the Page 2 Next and Page 2 Back shapes by clicking the dash. Update the Page 2 bookmark. Follow these same steps for the remaining pages of your report.

Do or do not. There is no try.

That’s it! You now have a fancy paginated report in Power BI. Don’t forget to let report owner know just how much work it was to create this report and maybe next time they’ll let you off with just a Stacked column chart 😉

Inspiration and Thanks

I want to thank Gilbert over at fourmoo for his excellent post which gave me the idea for this one.


Eric Fait

Senior Consultant

Share This Article