How to let users search by multiple fields without hitting delegation limits or performance walls.
Your Dataverse table has two million rows. Users want one screen where they can search by reference number, status, date, customer name, and more.
It works perfectly in testing with 300 records. In production, results quietly go missing, and nobody gets an error.
This post explains why that happens and how to design the search so it never does.
First, what is delegation?
Delegation means Power Apps sends the query to the data source (Dataverse, SQL, SharePoint) to process, and only the matching rows come back. If a function can't be delegated, Power Apps downloads only the first 500 records (adjustable up to 2,000) and filters those locally.
The result is incomplete, and there is no error.
You search 2M records for Status = "Open" and 10,000 match. Dataverse does the filtering and returns all 10,000, page by page.
Power Apps checks only the first 500 to 2,000 rows and silently misses the rest. The user sees a short list and assumes it's complete.
A blue underline and a warning triangle appear in Studio on non-delegable formulas. Treat them as errors, not suggestions.
Delegable vs non-delegable by data source
| Function / operator | Dataverse | SQL Server | SharePoint | Excel |
|---|---|---|---|---|
| Filter, LookUp | ✅ | ✅ | ✅ | ⚠️ limited |
| =, <>, <, >, And, Or | ✅ | ✅ | ✅ (some column types restricted) | ⚠️ |
| StartsWith | ✅ | ✅ | ✅ | ❌ |
| in (contains) | ✅ | ❌ | ❌ | ❌ |
| Search() | ✅ (with Dataverse search enabled) | ❌ | ❌ | ❌ |
| Sort, SortByColumns | ✅ | ✅ | ✅ | ⚠️ |
| Sum, Average, Min, Max, CountRows | ✅ (with limits) | ✅ | ❌ | ❌ |
| Left, Mid, Len, Lower on a column | ❌ | ❌ | ❌ | ❌ |
| ForAll, AddColumns, GroupBy, First, Last on source | ❌ | ❌ | ❌ | ❌ |
Support varies by connector and version, so always trust the delegation warning in Studio over any table, this one included. And Excel is not a real database. Don't use it for anything large.
The design principle
In a Canvas App, delegation is the first thing I design around. With 2M records, I never bring data into the app. I make sure every search runs on the server.
Step 1: Use delegable building blocks
Filterwith=,And,Or, andStartsWithon textinorSearch(with Dataverse search enabled) for "contains" matching- Choice, Lookup, Date and Number columns for filters instead of free text
LookUpfor single records
Here is a multi-field search where every part is delegable. Each filter is skipped when its control is empty:
Step 2: Add methods that protect performance
Delegable formulas stop wrong results. These habits stop slow ones.
Step 3: Verify it
- No delegation warnings anywhere in Studio.
- Power Apps Monitor shows one Dataverse call with a
$filterand a small payload. - A test record located beyond row 2,000 is still found. This is the test that proves delegation is really working.
Non-delegable searches look fine on small test data. Only a record deliberately placed past the row limit will reveal that results are being cut off.
Interview answer
If you're asked this in an interview, here is the short version:
With a 2M-record table, delegation is the main concern, because non-delegable formulas only process the first 500 to 2,000 rows and silently return incomplete results. So I push all filtering to Dataverse using delegable functions like Filter, StartsWith, in and Choice or Lookup columns, and I avoid things like Left, Len, ForAll or collecting the whole table. To keep it fast, I require at least one filter or a minimum number of characters, trigger the search from a button, and use a concatenated search-key column or Dataverse search for a single search box. I validate with Monitor and by testing records beyond row 2,000.
Never bring the data to the app; send the question to the data. Filter on the server with delegable functions, force users to be selective, and prove it works with a record that sits beyond the row limit.
