Canvas apps often work perfectly with a small test dataset and then show incomplete results after users start working with thousands of records. One common reason is delegation. Delegation allows Power Apps to send a supported query to the data source so the source filters or sorts the full dataset instead of the app downloading a limited number of rows and processing them locally.
This guide explains how delegation works, how to recognize non-delegable formulas, and how to redesign common gallery searches. The examples use a table named Orders with text columns named StatusText and OrderNumber. Delegation support varies by connector and data type, so verify each formula with the data source you use.
1. What delegation means in Power Apps
Suppose your data source contains 30,000 orders and your gallery uses a formula that Power Apps cannot delegate. The app may retrieve only the first portion of the data—500 records by default, or up to 2,000 if the app’s data row limit is increased—and evaluate the rest locally. Records outside that retrieved portion are not considered by the local operation, so a matching order can be missed.
With a delegable formula, Power Apps translates the supported operation into a query understood by the connector. Dataverse, SQL, or another source then performs the filtering or sorting on the server and returns the matching result set in pages as the app requests it.
2. Find delegation warnings before release
- Open the canvas app in Power Apps Studio.
- Select the control and inspect formulas such as
Items,OnSelect, or a property that queries a data source. - Look for the blue underline or delegation warning indicator in the formula editor.
- Open the warning details to identify the function or expression that cannot be delegated for that connector.
- Test with records beyond the configured data row limit, not only with a small development dataset.
A formula without a warning is not a substitute for testing. Connector capabilities, column types, and combinations of operators matter. Review Microsoft’s delegation reference for the exact functions supported by your connector.
3. Replace a local search pattern with a delegable filter
A common mistake is loading a full table into a collection and then searching that collection. For example:
ClearCollect(
colOrders,
Orders
);
Filter(
colOrders,
StatusText = "Open"
)
The collection is local. If the initial data retrieval is limited, filtering the collection cannot recover rows that were never loaded. When the connector supports the expression, filter the data source directly in the gallery:
Filter(
Orders,
StatusText = "Open"
)
For a Dataverse or SQL table, equality comparisons on supported columns are commonly delegable. Confirm the actual column type and connector support in your app. For example, a Dataverse Choice column is not a text column; use the appropriate Choice value comparison rather than comparing it to the text string "Open".
4. Build a searchable gallery with supported operations
For a text column where the connector supports StartsWith, combine it with a delegable equality filter. Set the gallery’s Items property to:
Filter(
Orders,
StatusText = "Open" &&
StartsWith(OrderNumber, txtSearch.Text)
)
This example filters open orders and matches order numbers that begin with the search text. It is not a universal guarantee: delegation depends on the connector, column type, and supported expression. Check the formula warning and connector documentation. If the search box is blank, StartsWith with an empty string may match all values, which is often suitable for showing all open orders.
If you need a contains-anywhere search, do not assume that Search or in will delegate for every connector. Check the current delegation matrix. For large datasets, consider changing the user experience to prefix search, adding a server-side search endpoint, or using a data source feature designed for full-text search.
5. Keep sorting on the data source
Sorting can also become local if the selected expression is unsupported. Where the connector supports it, combine filtering and sorting directly against the source:
SortByColumns(
Filter(
Orders,
StatusText = "Open"
),
"OrderNumber",
SortOrder.Ascending
)
Use the actual column name expected by SortByColumns and confirm that the column’s type and connector support server-side sorting. Avoid first copying the source into a collection and sorting the collection when the requirement is to browse the complete remote dataset.
6. Understand the data row limit correctly
In app settings, the non-delegable data row limit can be configured from 1 to 2,000; the default is 500. Increasing it may help during diagnosis, but it does not make a non-delegable formula delegable. It only increases the maximum number of records retrieved for local processing.
Do not set the limit to 2,000 and treat the warning as resolved. If the source contains 50,000 records, a local operation can still miss matches beyond the first retrieved subset. Fix the formula or move the operation to a server-side query.
7. When collections are still appropriate
Collections are useful for temporary UI state, small reference datasets, offline scenarios, or data that has already been intentionally narrowed on the server. They are not a safe replacement for querying a large remote table. If you need a local collection, first apply a delegable filter to the source so the downloaded set is intentionally bounded:
ClearCollect(
colMyOpenOrders,
Filter(
Orders,
StatusText = "Open" &&
OwnerEmail = User().Email
)
)
This is an example pattern, not a guarantee that every part delegates for every connector. Check whether the comparison against OwnerEmail and the use of User().Email are supported by the chosen source. Also consider whether a collection is necessary at all; a gallery can often use the filtered source directly.
8. Common delegation mistakes
- Loading the entire table into a collection: the initial retrieval may be limited before local filtering begins.
- Ignoring the warning because test data is small: incomplete results often appear only at production scale.
- Increasing the row limit as the final fix: this changes the local retrieval cap, not the formula’s delegation support.
- Using a function without checking its connector support: the same formula may delegate for one source and not another.
- Comparing a Choice column as text: use the correct typed value for Dataverse Choice fields.
- Combining many expressions without checking the whole formula: one unsupported part can cause local processing.
- Assuming a gallery displays every record at once: galleries load and render data as needed; validate the query results, not just the initial visible rows.
9. Production testing checklist
- Identify the connector and confirm the delegation support for every function and operator used.
- Check for delegation warnings on gallery and data-query formulas.
- Test with more records than the configured non-delegable row limit.
- Create a known matching record beyond the first 500 and verify that the app can find it.
- Test blank search text, partial text, no matches, and large result sets.
- Verify sorting and filtering with the actual column types in the target environment.
- Measure load time and confirm that the app is not downloading unnecessary rows.
Conclusion
Delegation is essential when a canvas app works with large data sources. Query the source directly, prefer connector-supported filters and sorting, and treat collections as intentionally bounded local data. Most importantly, test beyond the data row limit and verify the delegation behavior for the exact connector and column types used in production.
Microsoft documentation: Understand delegation in a canvas app.