Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
a4
Helper III
Helper III

Pagination in table visual

I have around 700 records in total.I want to achieve pagination in table visual(50 records per page).Is there any workaround for this?Any help is appreciated.

18 REPLIES 18
v-gizhi-msft
Community Support
Community Support

hi,

 

According to your description, please try this visual: Grid by MAQ Software.

You can import this custom visual to your report follow: https://docs.microsoft.com/en-us/power-bi/developer/power-bi-custom-visuals#import-power-bi-visuals-....

 

And it shows as follows.

j-1.PNG

You can set the max rows per page you want.

j-2.PNG

Here is my pbix test file.

pbix 

 

If you still have questions about this, please for free to let me know.

 

Best Regards,

Giotto Zhi

Hello friend, I have followed your instructions and it ran fine, thank you. But I have an idea that is to get the max rows adjusted directly with a selected button. For example, clicking on selected will dropdown a value like 10, 20 and when selected, the max rows will change. Please help me, thank you, sorry for bothering.

 

Annotation 2020-08-12 115911.png

Thank pro

But this visual doesn't support Hyperlink, any other solution....

hi,

 

According to your description, please continue to use table visual and create a measure to show its pagination effect.

Here is my test table:

41.png

I have six rows and it try to show them with five rows per page.

Please follow these steps:

1)Create a new column and a measure:

Parameter = GENERATESERIES(1, INT(MAX('Table'[ID])/5)+1, 1)

Parameter Value = SELECTEDVALUE('Parameter'[Parameter])

2)Create a measure to paginate the table:

FilterMeasue = IF(VALUE(SELECTEDVALUE('Table'[ID]))>=([Parameter Value]-1)*5+1 && VALUE(SELECTEDVALUE('Table'[ID]))<= [Parameter Value]*5,1,-1)

And then add it to filters:

42.png

3)Create a slicer by using the column above:

43.png

4)And the result shows:

44.png45.png

Here is my pbix test file.

pbix 

If you still have questions about this, please for free to let me know.

 

Best Regards,

Giotto Zhi

My Table structure is different from yours. I have a rank measure and I want to show first 50 ranks on the first page and so on.

Hi,

 

According to your requirement, I create a rank measure to test:

rank = RANKX(ALL('Table'),CALCULATE(SUM('Table'[sales])),,DESC)

 

Please change the original measure ‘FilterMeasure’ to this:

FilterMeasue = IF([rank]>=([Parameter Value]-1)*5+1 && [rank]<= [Parameter Value]*5,1,-1)

 

The following steps is the same with my second reply, and the result shows:

21.PNG22.PNG

Here is my pbix test file:

pbix 

If you still have questions about this, please for free to let me know.

 

Best Regards,

Giotto Zhi

I don't have any ID field upon which I can write the parameter logic. Is there any way to write the parameter logic on rank.Kindly request you to reply as soon as possible

 

Hi,

 

Please change the table [parameter] to this:

Parameter = GENERATESERIES(1, INT(MAXX('Table',[rank])/5)+1, 1)
 

And then if you remove [ID] column, it will still work because I have changed the original ‘FilterMeasure’:

Original:

FilterMeasue = IF(VALUE(SELECTEDVALUE(Table[ID]))>=([Parameter Value]-1)*5+1 && VALUE(SELECTEDVALUE(Table[ID]))<= [Parameter Value]*5,1,-1)

 

New:

FilterMeasue = IF([rank]>=([Parameter Value]-1)*5+1 && [rank]<= [Parameter Value]*5,1,-1)

 

The new measure depends on measure [rank] instead of column [ID], and the measure [rank] is not related to column [ID].

So removing column [ID] has no effect, and the result shows:

41.png42.png

If you still have questions about this, please for free to let me know.

 

Best Regards,

Giotto Zhi

Please guide me to generate the parameter as I don't have any ID field in my dataset.I did apply a logic by taking a constant number (747) as the last value to generate my series but as soon as change my page the rank again starts from1.

 

Please find attatched the screnshot for the same.Screenshot (155)_LI.jpgScreenshot (156)_LI.jpg

Hi,

 

For your problem, I think it is related to ‘ALL’ and ‘ALLSELECTED’ in your rank measure.

Here is my original rank measure:

rank = RANKX(ALL('Table'),CALCULATE(SUM('Table'[sales])),,DESC)

And the result shows:

41.png42.png

Now I try to change this  rank measure to this:

rank = RANKX(ALLSELECTED('Table'),CALCULATE(SUM('Table'[sales])),,DESC)

And the result shows:

51.png52.png

It should be noticed that the first page is normal but the second page’s rank field is incorrect.

The wrong result is just like the image shows you provided.

Please check your rank measure whether using ‘ALLSELECTED’, if so, try to change it to ‘ALL’.

 

If you still have questions about this, please for free to let me know.

 

Best Regards,

Giotto Zhi

I can't replace ALL with ALLSELECTED because i have to rank based on the filters in my report.Please find attatched the screenshot for the same.

 

Screenshot (157)_LI.jpg

Hi,

 

According your [New Rank] measure, please try to make a small change to it:

New Rank = ([Parameter Value]-1) * 5 + [rank]

 

Here is my original rank and changed rank:

101.png102.png

Please use the [New Rank] to replace the original [rank] to display.

 

If you still have questions about this, please for free to let me know.

 

Best Regards,

Giotto Zhi

Hi Giotto

Thanks for the solution.It's working fine but as soon as I change the page no. to 2... and so on tyhe filters(slicer) are not working.Please find the screenshot for the same.

 

Screenshot (158)_LI.jpg

 

Kindly request you to reply as soon as possible

Hi,

 

Please create a new rank measure instead of directly adding ([Parameter Value]-1)*5 to your original rank.

 

Best Regards,

Giotto Zhi

Hi Giotto

 

Please ignore my latest reply, I was able to filter out records based on  the Country when I am on third page, but the problem is I want my page slicer to adjust the page number according to the number of records.

 

Suppose after filtering on the basis of country or any other filter the total number of records are 100, then only two pages should be displayed in page slicer(50 RECORDS per page). Please find the screenshot for better understanding.

 

pagination.JPG

 

In the above screenshot after filtering the above records on the basis of country the page number should be reduced to two because there are only 51 records which are having France as their country and also after applying the filter the page no. should be automatically updated to one follwed two and so on depinding on the number of records(50 records per page).

 

Please reply as soon as possible.

 

Kind Regards

Amit Kumar

 

 
 

 

 

 

 

 

Hi,

 

I add some new data to my original test table:

42.PNG

Please create a new measure:

IsVisible = IF([Parameter Value]<COUNTROWS('Table')/5+1,1,-1)

Then add it to this visual filter:

 41.PNG

Then i test it, when i choose one country, it shows:

43.PNG

 

45.PNG

 

If my reply has answered your question, do not remember to mark my reply as a solution.

 

Best Regards,

Giotto Zhi

Hi Giotto 

 

IsVisible = IF([Parameter Value]<COUNTROWS('Table')/50+1,1,-1) doesn't work for me.Please find below the series of dax which I have used untill now:

 

1.Parameter = GENERATESERIES(1, INT(760/50)+1, 1)(for generating the series,I have used a constant number 760)
2.Parameter Value = SELECTEDVALUE('Parameter'[Page no.])
3.FilterMeasue = IF([New Rank]>=([Parameter Value]-1)*50+1 && [New Rank]<= [Parameter Value]*50,1,-1) (this is applied on visual level filter)
New Rank(Latest) = ([Parameter Value]-1) * 50 + [New Rank] (New rank measure)
 
Please go through my previous reply for problem scenario.Kindly reply as soon as possible.
 
Kind Regards 
Amit Kumar
 

How did you handle a web data source with a API that Paginates. It only brings back a limit of about 200 records. I have more records that needs to be retrieved from the API. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.