BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
Posted by Sam Lowrie on 01 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 with slicers
When there is loads of data in a table, it can be overwhelming to look at. This can lead to data being missed. Breaking the data into smaller chunks makes it more manageable:
Page 1 of the town sales table.
This blog goes through the steps you'll need to follow to create the pagination effect above.
Creating the table
The first thing to do is to create a table containing the page numbers you want to use. Click on the Enter Data option on the Home tab, call your table Page List and add as many rows as you will require pages for:
The easiest way to work this out is to divide the number of rows in the table by the number of rows you want per page. The Town table has 203 rows, which at 10 rows per page gives 21 pages.
Highlight the Page field and use the Modelling tab to turn it into text:
Text fields make for a much better looking slicer.
That is the easy part done and already you can make the slicer in the above picture. No relationships between the tables are required!
Numbering the rows
The next thing to do is to number the rows (in our case, the towns). Here's one way to do this:
In Query Editor go to the Add Column tab and click Index Column. Choose From 1 as From 0 will cause extra problems.
This will generate a unique list of numbers 1 to N for each town. Doing it in the query editor means any time the data is reloaded, the index will be redone.
The problem with this index (and indeed any calculated column) is that it is static. Adding a filter into the mix (for example, to show only towns in a particular region) will mess up my numbering.
Because of this problem, we will create our own numbering using a measure. To do this we need to create a temporary filtered table for each row of the table:
ALLSELECTED returns the town table filtered by external sources (such as slicers). FILTER takes this table and removes any towns which have a higher index number than the current row. CALCULATE then counts how many rows are left in the FILTERED table.
Here are three tables filtered to reflect what is going on for the Aylesbury row (index number 7):
On the left is the Town table filtered by external sources (in this case, a region slicer). This table is then filtered to show all Index numbers less than or including Aylesbury (7) (the middle table). The number of towns left are counted and assigned to Aylesbury as its row (4).
This gives us a visual row number which will change depending on the filters and slicers used on the table.
Calculating page numbers
You can now use this row number measure to calculate page numbers:
This will be different depending on how many rows you want per page. I want 10 rows per page, so I divide the row number by 10 and then use CEILING to round up to the first whole number (i.e 0.1 becomes 1).
My table now looks like this:
There are currently no filters for this table (hence why the Index and Visual row are the same).
Creating the slicer
Now we want to create the slicer and link it to our Page Number measure. Use the Page column to create a slicer:
In the format section of the slicer under General set Orientation to Horizontal to get this slicer format.
We can test if a value in the slicer has been checked by testing which values are still available in the Page List table:
The FORMAT function converts the Page Number into text. The formula then checks if this text exists in the Page List table (if anything on the slicer has been checked it will be in the table).
If the Page Number is present in the Page List table, then it will be returned in the measure; otherwise it won't be returned:
When page 2 in the slicer is ticked, the page numbers in the Test Page Slicer measure are only returned if they are 2.
If I remove all of the columns apart from TownName and Test Page Slicer then this is what it looks like:
Only rows that were on the ticked pages are returned.
Showing the units sold
Now if I want to return the units sold in these towns all I need to do is create a final measure:
As long as a value is returned from Test Page Slicer then we want the sum of quantity. If a value is not returned it hasn't been ticked, therefore we want nothing!
The final result
After all that we now have all our data on different pages within the same visual, and it can be filtered by other slicers:
These are the sales for towns in the North on pages 1 and 2.
We do have to include Test Page Slicer in the visual, but you can set the column width to invisible and turn Word Wrap off:
Just drag the column width to make it small like you would in Excel. Set Column Headers word wrap to off in the format window.
We did that function in loads of separate measures, but the truth is you could have done it in one:
Although I think we can agree that for learning it the other way was easier! Now all you need is an index number in each table you want the pagination in. Also remember to update the Sum of Quantity measure to All in one rather than Test Page Slicer.
If you want to see this report click here.