Filter, Search, and Lookup functions in PowerApps compared.

These are functions to find one or more records in a table.

*The formula is evaluated for each record of the table. Records that result in true are included in the result. Besides the normal formula operators, you can use the in and exactin operators for substring matches.

Fields of the record currently being processed are available within the formula. You simply reference them by name as you would any other value. You can also reference control properties and other values from throughout your app.

The Search function finds records in a table that contain a string in one of their columns. The string may occur anywhere within the column; for example, searching for “rob” or “bert” would find a match in a column that contains “Robert”. Searching is case-insensitive. Unlike Filter and LookUp, the Search function uses a single string to match instead of a formula.

“When to use” examples

Duplicate detection based on more than one column – Filter.

Retrieve a subset of the dataset filtered by one or more than one column – Filter.

Text search case-insensitive in multiple columns – Search.

Link to tables via reference – Lookup.

Delegation

When possible, PowerApps will delegate filter and sort operations to the data source and page through the results on demand.

Simple,

no delegation: retrieves a subset of records, performs functions and operations on that subset of records.

delegation: retrieves a subset of records, performs functions and operations on whole dataset, then retrieve a result subset.

For example,

Whole data set contains the record for Adam Smith.  The default subset for 500 records doesn’t contain the Adam Smith record. The function Search for “Adam” string will NOT retrieve the Adam Smith record if:

• The data source doesn’t support delegation
• The function/data source combination doesn’t support delegation.

It will only search in the subset of records.

Data sources vary on what functions and operators they support with delegation. If complete delegation of a formula isn’t possible, the authoring environment will flag the portion that can’t be delegated with a warning. When possible, consider changing the formula to avoid functions and operators that can’t be delegated. The delegation list details which data sources and operations can be delegated.