Is there any possible way to limit the no.of of rows dynamically in Power BI desktop - Table Visual

[Malarvizhi_P_R]

I need to limit my table view by 20k or 30k rows of data as default view in Power BI report level table view. When there is no slicer filter applied on the table but when the slicer filter are applied the table should display that particular filter combination data with 20k -30K. Is it possible?. Please let me know the inputs on this or some alternative solution.

[bhanu_gautam · Accepted solution]

@Malarvizhi_P_R ,You can try using RANKX and ISFILTERED

First create a RANK column, a calculated column

Rank = RANKX(ALL('YourTable'), 'YourTable'[YourSortingColumn], , ASC, DENSE)

Then create a measure for filter 

FilterMeasure =

   IF(

       ISFILTERED('YourTable'[YourSlicerColumn]),

       1,

       IF(

           'YourTable'[Rank] <= 20000, 1, 0

       )

   )

Use the FilterMeasure in the visual-level filters pane of your table visual. Set the filter to show only rows where FilterMeasure equals 1.

[bhanu_gautam · Accepted solution]

@Malarvizhi_P_R ,You can try using RANKX and ISFILTERED

First create a RANK column, a calculated column

Rank = RANKX(ALL('YourTable'), 'YourTable'[YourSortingColumn], , ASC, DENSE)

Then create a measure for filter 

FilterMeasure =

   IF(

       ISFILTERED('YourTable'[YourSlicerColumn]),

       1,

       IF(

           'YourTable'[Rank] <= 20000, 1, 0

       )

   )

Use the FilterMeasure in the visual-level filters pane of your table visual. Set the filter to show only rows where FilterMeasure equals 1.

[Malarvizhi_P_R]

I don't have any unique key in fact table. Even after creating composite key. fact table rows don't have unique key. The table visual has columns from dimensions table there no specific column for me apply the filter on top of this I need apply this condition to limit my table view by 20k or 30k rows of data as default view in Power BI report level table view. When there is no slicer filter applied on the table but when the slicer filter are applied the table should display that particular filter combination data with 20k -30K. Kindly let me know your the inputs on this or some alternative solution

[Anonymous · Accepted solution]

Hi @Malarvizhi_P_R 

You can create an index column in power query:

You can find Index Column in Add column pane.

So that you can use the workaround from @bhanu_gautam.

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.