The Complete Guide to Delegation in Power Apps

What Delegation Actually Means

Delegation is the single most misunderstood concept in Power Apps canvas apps. Here is the core idea: when your app queries data, either the data source does the filtering or your app does. Delegation is when the data source does the work.

When a function is delegable, Power Apps translates your formula into a query that the data source executes server-side. The data source filters millions of rows and returns only the matching results. This is fast and scalable.

When a function is not delegable, Power Apps pulls a batch of rows from the data source into the app’s local memory and then filters them locally. The problem? That batch has a hard limit.

The Row Limit: 500 or 2000

By default, Power Apps only retrieves the first 500 rows for non-delegable queries. You can increase this to 2000 in the app’s settings (Settings > General > Data row limit for non-delegable queries), but that is the absolute ceiling.

This means if you have 5000 SharePoint items and use a non-delegable function like Search(), your app will only ever see the first 500 (or 2000) items. The remaining items are silently ignored. No error is thrown at runtime — the app just returns incomplete results.

This is why delegation matters. It is not a performance optimisation. It is a correctness issue. Your app can return wrong results without any visible indication to the user.

The Blue Warning Banner

When you write a formula that cannot be delegated, the Power Apps Studio editor shows a blue underline with a warning triangle. The tooltip reads something like “The filter function’s ‘Search’ operation is not supported by delegation and will be evaluated locally.”

Never ignore this warning. It is telling you that your formula will silently return incomplete data if your data source has more rows than the data row limit. Treat it the same way you would treat a compiler error — something that must be resolved before publishing.

Delegable Functions by Data Source

Not all data sources support the same set of delegable operations. Here is a practical reference for the three most common sources:

Dataverse

Dataverse has the broadest delegation support of any Power Apps data source.

Delegable functions:

• Filter() — with most comparison operators (=, <>, <, >, <=, >=)
• Sort() and SortByColumns()
• Search() — yes, Search is delegable with Dataverse
• LookUp()
• in operator (for choice columns and text contains)
• StartsWith()
• IsBlank() / IsEmpty() on columns

Not delegable with Dataverse:

• EndsWith()
• Trim(), Lower(), Upper() inside Filter conditions
• Nested Filter() on related tables (some scenarios)
• CountRows() when combined with complex filters

SharePoint

SharePoint has significantly more limited delegation support than Dataverse.

Delegable functions:

• Filter() — with =, <>, <, >, <=, >= on indexed columns
• Sort() — single column only
• SortByColumns() — single column only
• LookUp()
• StartsWith() — on single-line text columns
• IsBlank()

Not delegable with SharePoint:

• Search() — this is the big one that catches everyone
• in operator
• EndsWith()
• Filter() with OR conditions across different columns
• CountRows() with filtered results
• Sort() on calculated columns or multi-value columns

SharePoint column indexing requirement: For delegation to work on SharePoint, the columns you filter on must be indexed. Go to your list settings and create indexes on any column used in Filter() or LookUp() formulas. Without an index, SharePoint may fall back to non-delegable behaviour on lists exceeding the list view threshold (5000 items).

SQL Server

SQL Server delegation support is good but has some quirks.

Delegable functions:

• Filter() — most operators
• Sort() and SortByColumns()
• LookUp()
• Search() — delegable
• StartsWith()
• in operator

Not delegable with SQL Server:

• EndsWith()
• String functions inside filter conditions (Trim(), Lower(), etc.)
• CountRows() with complex filters

Quick Reference Table

Function	Dataverse	SharePoint	SQL Server
Filter (=, <>)	Yes	Yes*	Yes
Filter (AND)	Yes	Yes*	Yes
Filter (OR)	Yes	Partial	Yes
Sort	Yes	Single col	Yes
Search	Yes	No	Yes
StartsWith	Yes	Yes	Yes
EndsWith	No	No	No
LookUp	Yes	Yes	Yes
in (text)	Yes	No	Yes
CountRows	Partial	No	Partial

*Requires indexed columns for lists over 5000 items.

Practical Workarounds

When you encounter a delegation limitation, you have several options. Here they are, ranked from best to worst:

1. Restructure Your Formula (Best)

Often you can rewrite a non-delegable formula as a delegable one. Common transformations:

Instead of Search() on SharePoint, use Filter() with StartsWith():

// Non-delegable with SharePoint:
Search(Products, TextInput1.Text, "Title", "Description")

// Delegable alternative (partial match on one column):
Filter(Products, StartsWith(Title, TextInput1.Text))

This is not a perfect replacement — Search() does substring matching across multiple columns while StartsWith() only checks the beginning of one column. But it is delegable.

Instead of OR across columns, use multiple filters combined with Concat or restructure:

// Non-delegable with SharePoint:
Filter(Tasks,
    Status = "Open" || AssignedTo = currentUser
)

// Potentially delegable — two separate filtered collections:
ClearCollect(colOpenTasks, Filter(Tasks, Status = "Open"));
Collect(colOpenTasks, Filter(Tasks, AssignedTo = currentUser));
// Then use colOpenTasks (but watch for duplicates)

2. Use Dataverse Instead of SharePoint

If delegation limitations are causing serious problems, consider whether Dataverse is a better data source for your scenario. Dataverse supports delegation for Search(), OR conditions, and many other operations that SharePoint does not.

The trade-off is that Dataverse requires a premium licence (Power Apps Premium) while SharePoint is available with standard M365 licences. For business-critical apps with large datasets, this is usually worth it.

3. The Collection Pattern (Use with Caution)

You can load data into a local collection in batches and then filter locally. This works around delegation but has serious limitations:

// On App.OnStart or screen's OnVisible:
ClearCollect(colAllProducts, Products);

// Then filter the local collection (no delegation needed):
Filter(colAllProducts, "widget" in Lower(Title))

Limitations of this approach:

• Only loads up to 2000 rows (the data row limit)
• Increases app load time significantly
• Data is stale — does not reflect changes made after loading
• Memory pressure on the client device

For datasets under 2000 rows, this is a reasonable approach. For anything larger, it simply moves the problem — you still miss rows beyond the limit.

4. The Paging Collection Pattern

For datasets between 2000 and approximately 10,000 rows, you can load data in pages using a loop:

// On a button or App.OnStart:
ClearCollect(colAllItems, []);
Set(varLastId, 0);

// Repeat this block (use a Timer or ForAll with a sequence):
ClearCollect(
    colPage,
    Filter(LargeList, ID > varLastId)
);
Collect(colAllItems, colPage);
Set(varLastId, Last(colPage).ID);

Warning: This is slow, complex, and fragile. It also hammers the data source with multiple requests. Only use this pattern when you have exhausted all other options and genuinely need to work with a SharePoint list larger than 2000 items in a canvas app.

5. Server-Side Filtering via Power Automate

For complex queries that cannot be delegated, you can offload the work to a Power Automate flow:

1. The canvas app calls a flow with the search parameters
2. The flow queries the data source using OData filters or SQL queries (no delegation limits)
3. The flow returns the filtered results to the app

// In the app, call the flow:
ClearCollect(
    colSearchResults,
    SearchFlow.Run(TextInput1.Text)
)

This approach has no row limit issues and supports complex queries, but it introduces latency (typically 3-10 seconds for the flow to execute) and requires a Power Automate licence.

Testing for Delegation Issues

Delegation issues are insidious because they only manifest with large datasets. Here is a practical testing strategy:

1. Check Every Blue Warning

Before publishing, review every formula with a delegation warning. Document each one and decide on an action: restructure the formula, accept the limitation (if data will always be small), or implement a workaround.

2. Test with Realistic Data Volumes

Never test only with 10 rows. If your production list will have 5000 items, test with 5000+ items. You can use Power Automate to generate test data quickly.

3. Set the Row Limit to 1 for Testing

Temporarily set the data row limit to the minimum value. This makes delegation issues immediately visible — if your filtered gallery shows only 1 item when there should be 50, you have a delegation problem.

In app settings: Settings > General > Data row limit for non-delegable queries > set to lowest value.

4. Monitor with the Performance Pane

The Power Apps Studio Monitor tool shows you every data request your app makes. Look for:

• getRows calls that return exactly 500 or 2000 rows (indicates hitting the limit)
• Multiple sequential calls to the same data source (indicates the paging pattern)
• Slow responses from the data source (indicates inefficient queries)

Common Gotchas

The Calculated Column Trap

Delegation does not work on SharePoint calculated columns. If you filter on a calculated column, the query is always non-delegable. Use a standard column with a flow to calculate values instead.

The Lookup Column Trap

Filtering on SharePoint lookup column values (not the ID, but the display value) is not delegable. Filter on the lookup ID instead:

// Non-delegable:
Filter(Tasks, Department.Value = "Finance")

// Delegable:
Filter(Tasks, DepartmentId = LookUp(Departments, Title = "Finance", ID))

The Date Comparison Trap

Date comparisons in Filter() are delegable with Dataverse but can be tricky with SharePoint. Always use explicit date values, not calculated dates:

// Potentially problematic:
Filter(Events, StartDate > Today() - 30)

// Safer:
Filter(Events, StartDate > DateAdd(Today(), -30, TimeUnit.Days))

The Concurrent Function Limitation

Concurrent() does not change delegation behaviour. Running two non-delegable queries concurrently just means you get two incomplete result sets faster.

CountRows and Delegation

CountRows(Filter(MyList, Status = "Active")) is not delegable with most data sources. If you need a count of filtered records, consider:

• Using Dataverse, which supports CountRows delegation for simple filters
• Maintaining a separate counter that a flow updates
• Using CountRows on a local collection (accepting the row limit)

When to Accept the Limitation

Not every delegation warning needs a workaround. If you can guarantee that your data source will never exceed 2000 rows, the non-delegable query will always return correct results.

Common scenarios where this is reasonable:

• Configuration tables (under 100 rows, rarely changes)
• Department lists, role lists, status option sets
• User preferences (one row per user, small org)
• Lookup/reference tables that are naturally bounded

Document these decisions. A comment in the formula explaining why the delegation warning is acceptable saves the next developer from trying to “fix” it:

// Delegation warning accepted: Categories list is capped at ~50 items
// and maintained by admin only. Will never exceed 500 rows.
Search(Categories, SearchBox.Text, "CategoryName")

Summary

Delegation is not optional knowledge for Power Apps development. Here are the key takeaways:

1. Non-delegable queries silently return incomplete data — this is a correctness bug, not just a performance issue
2. The 500/2000 row limit is absolute — no setting, workaround, or licence level increases it
3. Dataverse has the best delegation support; SharePoint has the most limitations
4. Always test with production-scale data volumes
5. When in doubt, restructure the formula to use delegable functions
6. Document every accepted delegation warning with a rationale