Instead of scrolling page by page, what about scrolling row by row instead? 

Picking Up Where We Left Off

Let’s reuse our dashboard and views from the previous Page by Page Vertical Synchronized Scrolling blog and make some small changes to go from page by page to row by row scrolling. We can keep the [Page] and [Page Size] parameters as they are. We still want 15 Countries per page, but the [Page] parameter is going to accept much higher values now. 

The [Max Page] calculation is going to change quite a bit. What we want to achieve is a form of endless scrolling; if there are less than 15 countries left at the bottom of the list, we want to start “backfilling” the list with countries from the top. However, there are 147 countries in our list, so the [Max Page] will equal our number of countries: {COUNTD([Country])}. 

Since [Page] cannot go over 147 and should start at 1 again, we leave [Next Page] and [Previous Page] as they are – they are limited by [Max Page] and that field we have already updated. So when we go to the next row when the [Page] parameter is set to 147, it will reset to 1. When we go to the previous page when on the [Page] parameter is set to 1, it will jump to 147. 

With our endless scrolling idea, we have one technical hurdle to overcome. At some point at the bottom of our list of countries, countries from the top of the list need to backfill our 15 countries visible at a time. This “cutoff point” is count of countries – (page size – 1)), or 147 – (15-1) or 133, since when we show country number 134 at the top, there aren’t 14 additional countries left until we reach the last country number 147. So at that point, countries number 1 and beyond need to appear at the bottom of the view again  

There are two ways to consider making this “backfilling” happen. The first way would be to create a filter that would filter the INDEX() based on the top and bottom first, but when there aren’t enough countries left at the bottom, create an additional clause that adds as many countries from the top of the list to ensure you always show 15 countries. In the calculation example, we first check if our [Page] parameter is bigger than 133 and if so, the filter will include everything from our [Page] parameter onwards as well as however many countries we need from the top. However, if our [Page] parameter is smaller than 133, we can just filter from that [Page] up until that 15 rows beyond. 

IIF([Page] + ([Page Size]-1) > [Max Page]

(INDEX () >= [Page] or INDEX () <= [Page Size] - ([Max Page]-[Page]) )

(INDEX () >= [Page] and INDEX () < [Page] + [Page Size]))

The problem with this is that when we go over the cutoff point, simply because we keep all countries in alphabetical order, additional countries from the top of the list will be added to the top of our view first. This does not feel right as this is not how endless scrolling should work. Countries should always be added to the bottom of the list, but there is nothing right now that will enforce this sort order. 

Our second option instead of filtering our list based on a static INDEX() – where Afghanistan will always be number 1 and Zimbabwe always be number 147 – is a “Moving Index,” where countries number 1 to 15 will change based on our [Page] parameter and the actual filter only considers our Moving Index from 1 to 15.  

Easy peasy, just take the INDEX() and subtract [Page] – 1. When [Page] is 10, our [Moving Index] start with 1 for Country number 10 based on the static INDEX():

The tricky bit of course is still when we move past the cutoff point of 133 and we need to backfill. The [Moving Index] needs to continue counting when continuing at the top of the list instead of showing an offset value of minus 130 and a bit. 

IIF(INDEX()-([Page]-1)<= (([Max Page] - [Page Size]) *-1) 

(INDEX()-([Page]-1))+ [Max Page] 

INDEX()-([Page]-1))

Translation again, if the static INDEX() (1 is Afghanistan and 147 is Zimbabwe) minus the [Page] parameter -1 is less than or equal to negative 133 (note, the negative value of our cutoff point), then add 147 to the INDEX() minus the [Page] parameter -1, otherwise just do INDEX() minus the [Page] parameter -1.  

When our page is set to for example 145, countries with a static INDEX() of 145 to 147 (Yemen to Zimbabwe) will have a [Moving Index] of 1 to 3 based on INDEX() – (145-1). 

The next 12 countries have a static INDEX() of 1 to 12, as they come from the top of our full list. They will have a moving index of 4-15, since the first one of these countries (Afghanistan) will first be calculated as 1 – (145-1) = -143, less than -133, so the calculation will add the 147 to make it 4. The last one (Barbados) will be calculated as (12 – (145-1)) + 147 = 14.  

We finish this all of with a simple filter using our [Moving Index]:

[Moving Index] >=1 and [Moving Index] <= 15

You can find the workbook published below. Keep an eye out for the next installment in this series!