How to build a dashboard like page which summarize the data from a SharePoint list which contain around 85,000 items

Question

I have a custom SharePoint online list named ServiceRequest, which contain around 85K++ items, as follow:-

The custom list contain those main custom fields:-

• Assigned To >> People or Group

• Start Date & End Date >> DateOnly

• Year >> Single line of text

• Status >> Choice contain values such as "Open", "Closed","In Progress"

Here is sample of the data:-

now i want to build a dashboard which should show the following info:-

1. Total number of items per year

2. For each year to show a bar charts with the number of items per status.

3. Have the ability to filter the items per year and per status

now is this possible to do so directly inside a Power Apps canvas application taking into consideration that we have 85K++ items? i am not sure if we need to do it directly inside Power Apps, or use a scheduled Power Automate flow or scheduled azure function which do these calculations (number of items per year and per status) and save these calculation inside a separate list for the Power apps to read them directly?

Answer 1

Consider using SharePoint search to surface this information. The data can be queried via the SharePoint search API without running in to list view threshold or performance issues. Once you have the data, you can then display it as required via your dashboard.

SharePoint Search REST API overview