How to Write a Filter Query in Power App?

Filtering data in Power Apps is primarily done using the built-in Filter function.

Understanding the Power Apps Filter Function

The Filter function allows you to select specific records from a data source based on criteria you define. It's a fundamental part of creating dynamic and responsive applications.

As per the basic syntax for the Filter function in Power Apps provided, it is Filter(Source, Condition).

Here's a breakdown of the components:

Source

This refers to the data source you want to filter. This could be:

A SharePoint list
A Dataverse table
An Excel table imported as static data or connected via a connector
An SQL Server table
A Collection created within your app

Condition

This is the criteria or expression applied to filter the data. The Condition must be a formula that evaluates to either true or false for each record in the Source. Only records for which the Condition is true will be included in the result of the Filter function.

You can use various operators and functions within the Condition to build complex filters.

Practical Examples of Filter Queries

The Filter function is most commonly used in the Items property of controls like:

Galleries
Data Tables
Combo Boxes

Here are some examples:

Filtering by Text Input: Display records where the 'ProductName' column contains the text entered in a TextInput control named TextInputSearch.
```
Filter( 'Products List', 'ProductName' In TextInputSearch.Text )
```
Explanation: This checks if the text from TextInputSearch exists anywhere within the ProductName field for each record.
Filtering by Dropdown Selection: Show orders with a status matching the selected value in a Dropdown control named DropdownStatus.
```
Filter( Orders, Status = DropdownStatus.Selected.Value )
```
Explanation: This compares the Status column of each record to the selected value from the dropdown.
Filtering by Date: Filter a list of events to show only those occurring after today.
```
Filter( Events, EventDate > Today() )
```
Explanation: Today() returns the current date without time. This compares the EventDate field to today's date.
Combining Multiple Conditions (AND): Display active tasks that are also high priority.
```
Filter( Tasks, Status = "Active" And Priority = "High" )
```
Explanation: Both conditions (Status = "Active") AND (Priority = "High") must be true for a record to be included.
Filtering by Number Range: Show products with a price between $10 and $50.
```
Filter( Products, Price >= 10 And Price <= 50 )
```
Explanation: Uses comparison operators to check if the Price falls within the specified range.

Common Filter Operators and Usage

Here is a table summarizing some common operators used in the Condition part of the Filter function:

Operator	Description	Example Formula
`=`	Equals	`Status = "Completed"`
`<>`	Not equals	`Category <> "Internal"`
`>`	Greater than	`OrderAmount > 500`
`<`	Less than	`OrderDate < Date(2023, 1, 1)`
`>=`	Greater than or equals	`Quantity >= 100`
`<=`	Less than or equals	`ExpiryDate <= Today() + 30`
`In`	Value is contained	`City In ["New York", "London"]`
`And`	Both conditions true	`Status = "Active" And DueDate < Today()`
`Or`	Either condition true	`Priority = "High" Or Priority = "Urgent"`

Key Considerations

Delegation: For performance with large data sources (like SharePoint or Dataverse), ensure your filter conditions are "delegable". This means the filtering is processed by the data source itself rather than Power Apps downloading all data first. Simple comparisons, And, Or, In, and certain string functions are often delegable. Non-delegable queries can retrieve a maximum of 500 (or up to 2000 with settings adjusted) records.
Data Types: Ensure you are comparing values of the same data type (e.g., text to text, number to number, date to date).

Using the Filter function effectively is key to building efficient and user-friendly Power Apps interfaces that display only the relevant information.

askvity