More suggestions for how to use bookmarks
Part five of a five-part series of blogs

Another set of examples showing ways in which to use the weird and wonderful bookmarks feature in Power BI Desktop

  1. More interesting suggestions for using bookmarks
  2. Creating dynamic charts
  3. Dynamic page design
  4. Information pop ups using bookmarks
  5. Pagination using bookmarks (this blog)

Posted by Sam Lowrie on 09 June 2018

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Pagination using bookmarks

Whilst this method creates a smooth transition between pages, it is rather time consuming. For a faster method not using bookmarks click here.

Long tables of data are a staple of most reporting systems, but normal doesn't necessarily mean good:

Tables long lists

It becomes very easy to lose track of data and miss patterns. Did I already look at Bootle, or is that new when I scrolled down?

 

What would be easier to read (although perhaps not to achieve) is to limit the number of rows to 10 and allow a user to switch between sets of 10. Like this:

Bookmarks Pagination

This way we can move methodically between the pages.

To do this we need a way of separating our list into pages such as an index number. The easiest way to do this is open the Query Editor from the Home tab: 

Query Editor Index column

On the Add Column tab choose Index Column then From 1. This creates a new column counting 1 to n for each row in the selected table.

At this point decide how many rows you want per page. I'm going with 10 per page, so I need a new calculated column to work out the page numbers:

Ceiling bookmarks pagination

Dividing by 10 and then rounding up to the nearest whole number will separate my rows into pages. For example 1-10 will all be on page 1.

At this point if you aren't fussy then there is a faster option than bookmarking - by creating a page number slicer:

Bookmarks pagination slicer

Admittedly this method does have the advantage of speed, and also gives the user the option of having more than one page showing.

Another method would be to use bookmarks which restrict access. We need a way of moving between these bookmarks, so first create forward and back arrows:

Bookmarks

Page 1 only need a forward and page 5 (which is my last page) only needs a back arrow.

Using the selection pane hide all the buttons that aren't going to appear on page one. Then add a Visual filter to the table for Page number 1:

Bookmarks pagination

After adding the filter create a bookmark of this page and call it something appropriate like...Page 1

Now comes the tedious part: repeat this for every page number you have, hiding the buttons you don't want and changing the page filter.

Finally attach the bookmarks:

Bookmarks slicer

While it gives a smoother transition than the slicer, it really does take a while to make!

This is a very simple version of pagination. To make it more complicated read this blog to create a dynamic measure.

To try this report click here.

This blog has 0 threads Add post