I have a report with over 5 million rows. People normally use a few filters and get the data down to less than 1k rows and then in some cases they will export the data.
I created a basic table at the bottom of the report with the 5-6 rows they need, the issue is that when there is no filter the table normally shows an error because one on the needed columns has a description that’s rather large.
My Goal…
I’m trying to find the best way to reduce this table so that it only shows a max of (5K rows). This will then stop the “resource error” from showing. Any idea on what the best practice is for doing this?
Instead of one file, could you split into several files for business/support area before it hits power bi? IE marketing, finance, ETL (Leadership-CEO level) etc. Have one format report for all of them.
As @Keith suggests, bringing in data based on categories is a great option. that will indeed help in avoiding this error.
if that somehow is not an option for you, then i have another 2 approaches that you could take.
Look into creating some kind of summary of categories / records on the first page and create a drill through to (lets say) page2, that just has this table. Drill through can carry all the required filters from page 1 to page 2
Second approach could be, to disable the "Show Select All " option in your slicers and have a default value there always.( probably the one that makes the table show less number of rows)
this means any time users load the report, it will load with a fixed filters already applied. and they need to change to whatever they want to select.
Not really, the whole dataset is based on incidents and anyone from any team can pull an incident report. One incident may affect multiple teams so we can not pre dice the data.
I ended up finding a solution that i think works very well.
I have my standard table with all the columns. One of the data points is “Incident Created Date/Time”. In the filter pane for this table I open the “Incident Created Date/Time” and use a TOP N filter. I set it to Top N 5,000 based on earliest date.
This will make the table show only the first 5,000 incidents based on Date (newest incidents). This works perfectly. I also call out in the table header that you can only download a MAX of 5,000 records.