How to Search 2 Million Dataverse Records in a Canvas App Without Delegation Issues

Power Apps

2M

Dataverse rows

Delegation
first

Practical Guide

Searching 2 Million Records
from a Canvas App

Design multi-field search that stays fast and never silently drops results

Delegation Dataverse search Performance

Power Apps Dataverse Canvas Apps OCT 2026

How to let users search by multiple fields without hitting delegation limits or performance walls.

The problem

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.

Delegable query

You search 2M records for Status = "Open" and 10,000 match. Dataverse does the filtering and returns all 10,000, page by page.

Non-delegable query

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.

How to spot it

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	❌	❌	❌	❌

Trust the warning, not the table

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.

User inputfilters + Search button

→

Delegable formulaFilter / StartsWith / in

→

Dataversefilters on the server

→

Gallerysmall page of rows

Step 1: Use delegable building blocks

• Filter with =, And, Or, and StartsWith on text
• in or Search (with Dataverse search enabled) for "contains" matching
• Choice, Lookup, Date and Number columns for filters instead of free text
• LookUp for single records

Here is a multi-field search where every part is delegable. Each filter is skipped when its control is empty:

Power Fx · Gallery Items

Filter(
    Orders,
    IsBlank(txtRef.Text) || StartsWith(ReferenceNo, txtRef.Text),
    IsBlank(drpStatus.Selected.Value) || Status = drpStatus.Selected.Value,
    IsBlank(dpFrom.SelectedDate) || OrderDate >= dpFrom.SelectedDate
)

Step 2: Add methods that protect performance

Delegable formulas stop wrong results. These habits stop slow ones.

Most important

1. Make it selective

Require at least one filter or a minimum number of characters. Use a Search button (or DelayOutput on the text input) instead of querying on every keystroke. A blank search returning 2M rows is what kills performance.

Avoid

2. Don't collect the table

ClearCollect(colOrders, Orders) is capped at the row limit and slow.

Pattern

3. Search-key column

Keep one column that concatenates the searchable fields (via a calculated column or a flow). Run a single StartsWith or in on it instead of ORing five columns.

Pattern

4. Quick Find + Dataverse search

Put searchable columns in the Quick Find view and enable Dataverse search for a "Google-style" single search box.

Quick win

5. Pull only what you need

Use ShowColumns to limit columns, and show results in a gallery.

Fallback

6. Server-side logic

For complex logic, use a Dataverse view, a plug-in, a flow or a custom API. Power users can also get a model-driven app with Advanced Find.

Step 3: Verify it

• No delegation warnings anywhere in Studio.
• Power Apps Monitor shows one Dataverse call with a $filter and a small payload.
• A test record located beyond row 2,000 is still found. This is the test that proves delegation is really working.

Why the third check matters

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.

The rule of thumb

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.