In this blog post I am going to demonstrate how to achieve Pagination in Power BI reports, using the new Bookmarking feature. I got this question from a follower on Twitter Murali Krishna

With the new bookmarking feature there now is a way to make it appear that you can page through Power BI reports.

Below is what it looks like in action.


Parameters

I used the following Parameter as shown below, which I then used later to automatically determine how many rows to show per page

How I got the Page Numbers based on the Rows Per Page Parameter

In this next section, what I did was to use my parameter with my data so that I could determine the page numbers dynamically.

By using the parameter this enabled the data to always get the right number of rows for each page dynamically.

  • This is what my data looked like at the start.
  • The first thing that I did was to add an Index by going into the Add Column ribbon and clicking on Index Column and then From 1
    • NOTE: The reason that I started at 1, was because when I am showing the rows it starts from 1
  • I then duplicated the Index Column, so that I could then use this to create the page number.
  • Next, I clicked on my column called “Index – Copy” highlighted below in Yellow
  • Then in the Transform ribbon I clicked on Standard and then Integer-Divide
  • I then manually put in a value of 10
  • Next, I wanted to change it from a static value to use my parameter value, so I did it by doing the following.
    • This is what it looked like with the Static value in the M Code
      Table.TransformColumns(#"Duplicated Column", {{"Index - Copy", each Number.IntegerDivide(_, 10), Int64.Type}})
    • I then changed it from 10, to my Parameter value, as shown below highlighted in PURPLE
      Table.TransformColumns(#"Duplicated Column", {{"Index - Copy", each Number.IntegerDivide(_, #"Rows Per Page"), Int64.Type}})
      • NOTE: The Parameter name is case sensitive.
  • The final steps was I added 1 to my column, so that once again the page number started from 1. And then I renamed it to reflect the page name
  • This is what it looked like once complete.
  • I now loaded this into my data model.

Creating the Pagination

I am not going to go into every step due to it will make this blog post too long, but I will demonstrate how I got the Pagination working.

  • The first thing that I did was to show the Bookmarks Pane.
  • Next what I did was to show my data based on the page number.
    • What this did is it enabled my data to be dynamic
      because it was based on the parameter, if I had to change how many rows to show to 25, due to the way I modelled the data in the Query Editor it would then have 25 rows per page.
  • I then created 4 bookmarks as shown below, with each being linked to a page number in the filter section.
  • I then also created measures to display the page that I am on.

    Pagination = “Page ” & SELECTEDVALUE(‘Sheet1′[Page Number],1) & ” of ” & [Max Page Number]

  • As well as the Records Measure

    Record Rows = ” Records ” & [Start Index] & ” to ” & [Max Index] & ” of ” & [Max Index Records]

  • Now the final piece, which does take some time to get right, was to enable the buttons to go forwards and backwards. So that when the user clicks on the buttons it appears that they are going forwards or backwards on each page.
    • I had to enable the Selection Pane Window
    • I had created an image for a Forward button and a Backwards Button
    • I then inserted my Forward Image.
    • NOTE: Always make sure that you are on the Bookmark Name where you want the interaction to take place.
      • I then clicked on the Forward Image and went to the Properties.
      • I then set the properties for the Link to type Bookmark and to Page 2 as shown below.
      • Then I went to the Bookmark called Page 1 and clicked on Update
      • I then went to the Bookmark called Page 2
        • I then went to the existing image and clicked on Hide
        • I then imported my Forward Image again.
        • I then clicked on the Forward Image and went to the Properties.
        • I then set the properties for the Link to type Bookmark and to Page 3 as shown below.
        • I then imported my Backward Image.
        • I then clicked on the Backward Image and went to the Properties.
        • I then set the properties for the Link type to Bookmark to Page 1 as shown below.
      • I then had to repeat this for all the other pages, as well as hiding the images on the different pages.
      • As well as ensure that the images were in the identical place as per the other images. This made it appear as it was the same button.
  • I then finally uploaded this to the Power BI Service and tested it to ensure that it worked as expected.

Conclusion

As you can see I demonstrated how to do Pagination in Power BI, it is a bit complex to get it right. But what I really like is that when it is done right, it appears seamless to the end user and VERY easy to use. Which is always my goal to make the end result as simple as possible.

Below is the report in action, if you want to click through and test it.