Limit the Table visual to show 10 rows per page

[ashraparas]

Hello,

I have tried many options such as DAX query, pagination (GRID), Filters to choose and limit the number of rows view in a table visual using power bi. But options like GRID does not help, when printing to pdf it prints only the first page.

I cannot filter page by page as its very manual and runs into multple pages then lesser.

For example  : my table looks like below but I want to cut it at the 10th row and move the remaining 10 to next page, another 10 records to the 3rd page, etc

Name            City            Short Bio                                                                                      Country

ABC               NYC           Actor working for 10 years in theatres, roadshows, series            USA

                                        He enjoys reading, travel and food

DEF               London     Lawyer working for 4 years and has a law firm consulting             UK

                                       Provides good serices across the country

Would greatly appreciate any help, guidance or insight to achieve this.

Thanks

[v-cgao-msft · Accepted solution]

Hi @ashraparas ,

You can try the new function.

ROWNUMBER – DAX Guide

Ranking = ROWNUMBER(ALL('Table'),ORDERBY('Table'[Name],ASC,'Table'[City],ASC))

You can then filter the table on each page as suggested, here is a sample file for your reference.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum 

[ashraparas]

Hi @v-cgao-msft 

Please could you also advise if the data contains historical data in the same file? I tried using this formula for an older file + new data in it but it does not work correctly. My number of rows go beyond 3000 and I am not able to get the latest quarter details using this filter.

[ashraparas]

Thank you very much, you have saved a number of hours I investigated earlier to find this solution. This works Excellent!!!!! 🙂

[Sergii24]

Are you asking for a help with PowerBI desktop? What exactly do you want to achieve?

Imagine you have 50 rows in your data source. Do you want to visualize a table visual on canvas that contains first 10 rows? What next, do you want to visuazile the following 10 rows in a separate table visual on the same page? Or do you want to create a new page in PowerBI desktop? 

[ashraparas]

Hello @Sergii24 - I want visualize 10 rows per page and Power BI to help me show next 10 rows in 2nd page, likewise another 10 rows in 3rd page, etc.

[Sergii24]

Thank you for clarification, @ashraparas. As of my knowledfge you can't programatically create report pages in Power BI based on loaded data from a data source. The number of pages must be defined by report developer in advance and remains static after data refresh.

[ashraparas]

Thanks @Sergii24  - is it possible using DAX formula to cut the data using say last 10th row of the page and start next page with the 11th row? Selva below has given the Rankx measure but its giving me an error.

[Sergii24]

By using DAX you can limit number of rows to display, let's say 1-10. You can have a separate calculation to show the next 10 items, let's say 11-20. However:

1. Number of such measures must be defined by you in advance: if you have 40 rows and 4 measures and the next day after refresh you get 50 rows because 10 new rows have been added to the data source you'd need to create a new measure for 41-50 items.
2. Same is valid for number of pages: it must be defined in advance and will not change dynamically.

If you provide more details on your task we might be able to help you and brainstorm together on an alternative 🙂

[ashraparas]

Hello @Sergii24  - Could you share the DAX formula which can limit the number of rows to display 1-10, then next page 11-20, etc. I am fine to create such page limit as do not expect major increase in the count of rows over the quarter. 

I cannot share the exact data but the above example is exactly as per my database. Does the limit need to be set using Name or Country? i.e. the 10th row name / country.

Thanks

[Selva-Salimi]

Hi @ashraparas 

which column or columns used to sort your table to recognize first 10 or second 10?!. use that column (lets call it value) and create another column in your table as follows:

ranked_column = RANKX('your_Table','your_Table'[value],,DESC)

then you can add this column in the page level filter and set your expectations in "Less than..." , "greater than".

If this post helps, then I would appreciate a thumbs up  and mark it as the solution to help the other members find it more quickly. 

[ashraparas]

Hi @Selva-Salimi - tried to add using New Measure option but I am getting error.

A single value for column 'Name' in table 'List' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

In the above example, If I am using 2 level sorting, 1 by Name (alphabetically - ascending) and 2 by City (descending)

[Selva-Salimi]

@ashraparas 

you should not write a measure. you should write a column. and also you can follow these steps if you cant write using rankx.

1. create a column by concatenate Name and City columns. (call Name_City)

2. create another column and use the column in previous steps as follows:

  calculate(count(Name_City) , filter (your_table, Name_city <= earlier(Name_city))

and then add this column in every page in page level filter

and then set it based on your expectations.

If this post helps, then I would appreciate a thumbs up  and mark it as the solution to help the other members find it more quickly.