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.

Hi Malarvizhi P R , Thank you for reaching out to us on Microsoft Q&A forum. Yes, it is possible to limit the number of rows in a Power BI table visual to 20k or 30k by default and have it dynamically adjusted when slicers are applied. Here's how you can achieve this: Add an Index Column In Power Query Editor: Open your dataset. Go to Add Column > Index Column > From 1 . Alternative Solution Using Top N Filter: Select your table visual. In the Filters pane, use the Top N filter option. Specify 20000 or 30000 as the number of top rows to display. This approach ensures that your table view remains manageable and performs well, both with and without slicer filters. If you are still facing any issue, please let us know in the comments. We are glad to help you. If the information is helpful, please Accept Answer so that it would be helpful to community members. Thank you.

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

Accepted answer

Syed Saleem Peera 5,720 Reputation points Microsoft Vendor

2024-07-23T05:53:15.9666667+00:00
Hi Malarvizhi P R,

Thank you for reaching out to us on Microsoft Q&A forum.

Yes, it is possible to limit the number of rows in a Power BI table visual to 20k or 30k by default and have it dynamically adjusted when slicers are applied. Here's how you can achieve this:

Add an Index Column

In Power Query Editor:

Open your dataset.

Go to Add Column > Index Column > From 1.

Alternative Solution

Using Top N Filter:

Select your table visual.

In the Filters pane, use the Top N filter option.

Specify 20000 or 30000 as the number of top rows to display.

This approach ensures that your table view remains manageable and performs well, both with and without slicer filters.

If you are still facing any issue, please let us know in the comments. We are glad to help you.

If the information is helpful, please Accept Answer so that it would be helpful to community members.

Thank you.
Please sign in to rate this answer.
Malarvizhi P R 20 Reputation points

2024-07-23T07:26:03.6466667+00:00

In Power Query Editor:

Open your dataset.

Go to Add Column > Index Column > From 1.

For this month Do we need to keep Index column in table and then apply the filter as 20000 or 30000

Malarvizhi P R 20 Reputation points

2024-07-23T07:29:11.9133333+00:00

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

Syed Saleem Peera 5,720 Reputation points Microsoft Vendor

2024-07-23T09:36:36.2066667+00:00

Hi Malarvizhi P R,

Thank you for your patience.

I understand that you don't have a unique key and the composite key isn't resulting in unique rows. Given this situation, here's an alternative approach to limit the table view to 20k or 30k rows by default and dynamically adjust when slicers are applied:

Approach:

To limit the table view without a unique key, we can create a calculated column to rank the rows and then use DAX measures to filter the top rows.

Steps:

1.Add an Index Column:

Since you don’t have a unique key, we will add an index column to create a unique identifier for each row.

Open Power Query Editor.

Add Index Column:

Go to Add Column > Index Column > From 1.

Close and Apply the changes to return to Power BI Desktop.

2.Create a Ranking Measure:

Use DAX to create a measure that ranks rows based on the Index column.

Ranking = RANKX(ALLSELECTED('YourTable'), 'YourTable'[Index], ASC, DENSE)

3.Create a Filter Measure in DAX:

DisplayRow =

VAR MaxRows = 20000 -- Change to 30000 if needed

RETURN

IF(

COUNTROWS(ALLSELECTED('YourFactTable')) <= MaxRows, 1, IF([Ranking] <= MaxRows, 1, 0)

)

Apply the Filter Measure:

Create a table visual in your report.

Add the required columns from your dimension tables.

In the Filters pane for the table visual, drag the DisplayRow measure.

Set the filter to show only rows where DisplayRow equals 1.

If you are still facing any issue, please let us know in the comments. We are glad to help you.

If the information is helpful, please Accept Answer so that it would be helpful to community members.

Thank you.

Syed Saleem Peera 5,720 Reputation points Microsoft Vendor

2024-07-23T13:47:19.2566667+00:00

Hi Malarvizhi P R,

Glad to hear that your issue got resolved. Please don't hesitate to reach out to us if you have any further queries.

Thank you.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

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

0 additional answers

Your answer