cancel
Showing results for 
Search instead for 
Did you mean: 
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.

17 REPLIES 17
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....

v-gizhi-msft
Community Support
Community Support

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.

v-gizhi-msft
Community Support
Community Support

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

v-gizhi-msft
Community Support
Community Support

 

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

v-gizhi-msft
Community Support
Community Support

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

v-gizhi-msft
Community Support
Community Support

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

v-gizhi-msft
Community Support
Community Support

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

 

 
 

 

 

 

 

 

v-gizhi-msft
Community Support
Community Support

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
 

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors