Power Platform Community Forum Thread Details

Hello all,

I am beginner to PowerApps and stuck with my first app deployed in production.

I have dropdown list which fetches items from SharePoint list with 8000+ records by doing some calculations. But due to the formula written in Item property of dropdown list, app is taking more than 3 minutes to load and page becomes unresponsive. How can i make the formula efficient or is there any other alternative to improve performance?

Below is the formula which fetches items from Projects list where Gate is the column indicating stages of a project. I want to laod all projects which are approved in Gate 1 and/or Gate 2 :

Sort(Distinct(Filter(Filter(Projects,(Gate="1" && Approval="Approved") || (Gate = "2" && Approval="Approved")).Title,Not(Title in Filter(Projects,Gate="3" && Approval="Approved").Title)),Title),Result,Ascending)

P.S: some times this dropdown does not shows all the values as i guess i am using Distinct in the function.

Categories:

General Questions

Connecting to Data

@uiit3155

If you are using a list >2k in SharePoint. you need to understand delegation in PowerApps. Only certain functions and operators are delegatable, meaning they perform at the Server level. Otherwise, PowerApps limits the number of records that it will work with at the Device level.

In your formula, Distinct(), "not" and "in" cannot be delegated to SharePoint and the record set they operate on will only be accurate if it is less than the 2k record limit set by PowerApps. In your case, if you can prefilter the data down to <2k records before using those operators, then your formula will provide accurate results and not take a performance hit. Otherwise you will have to consider using a different data source than SharePoint to avoid performance and accuracy problems.

With({proj:Sort(Filter(Projects, Approval="Approved"), Title, Ascending), // each condition must have 
 g3a: Filter(Projects, Approval="Approved" && Gate = 3), Title, Ascending) // <2k records
 }, 
 Distinct(
 Filter(
 proj, !(Title in g3a.Title)
 ), Title
 )
)

Give thought to using either Dataverse or Sql as the data can be pre-filtered at the Server level as a View and they can handle very large datasets,