Power Apps Filter function allows you to find a records in a table that match the specific condition criteria.
You can use more than one conditions to find a set of records in a table. If one or more records in a table match the condition criteria, it returns the records otherwise discard the records.
SYNTAX:
Filter(Table*, Formula1 [, *Formula2*, ... ] )
- Table – It is required. Table to search.
- Formula(s) – It is required. The formula by which each record of the table is evaluated. The function returns all records that result in true. You can reference columns within the table. If you supply more than one formula, the results of all formulas are combined with the And function.
Lets look at an example of using Filter function in Power Apps.
Here, we have a Gallery control named Gallery_Orders and it contains the Orders details.
Using Filter function to filter the gallery items
Let’s filter the records in gallery for Category Technology.
Go to the Items property of gallery control and write the below formula.
Filter(
Orders,
Category = "Technology"
)
Now, you can see, records in gallery are filtered for Category Technology.
Lets, add one more condition in above formula and filter the records for Segment Corporate.
Filter(
Orders,
Category = "Technology" && Segment = "Corporate"
)
Now, you can see the records in gallery are filtered for Category Technology and Segment Corporate.
Filter the gallery items based on a value in text box
You can create a search box to filter the gallery items based on value provided in search box. So, we will add a Text input control and make it look like a search box.
Let’s add a Text input control, go to Insert tab and select Text input and named it as txt_search.
Now, we will make it look like a search box. Go to Properties of Text input control.
In Default, remove the default text from. Next, provide the appropriate text for Hint text as we will filter the records for Category so give the Hint text as Search by Category.
Now, go to Items property of gallery and write the below formula.
It will filter will search the Category that start with the text in the search text box.
Filter(
Orders,
StartsWith(Category,txt_search.Text)
)
Lets, filters the records in gallery for Category Furniture.
You can see, it filters the records in gallery for category Furniture.
Note that, as we have used function Startwith which filters the records in gallery for the Category that start with the sequence of characters in search text box. So, it filters all the records for Category with start with work Furni.
Lets, add one for search criteria in this formula, and make it to filter the records for Country as well.
Now, this time you can provide either Category or Country value in search box and it will filters the records in gallery accordingly.
Filter(
Orders,
StartsWith(
Category,
txt_search.Text
) || StartsWith(
Country,
txt_search.Text
)
)
You can see, it filters the records in gallery for Country United States.
As, StartWith function filters the records that start with the sequence of characters in the search text box.
But in case, if you want to filter the records based on partial search means to filter the records in gallery for records in which the search text appears anywhere within the column then you can use IN operator.
Lets modify the above formula and convert it using IN operator as shown below.
Filter(
Orders,
(txt_search.Text in Category) || (txt_search.Text in Country)
)
Now, you can see it filters the records for Country.
As we typed the text State in search box and it returns all the records for Country United States because text State appears somewhere in Country name United States.
To filter the records for values selected in drop down
Lets, say we have a drop down control named ddl_Country. It is displaying countries.
Now, we want to filters the records in gallery for selected country in drop down.
Following formula will filters the records in gallery for selected Country in dropdown.
Filter(
Orders,
(Country = ddl_Country.Selected.Value || 
ddl_Country.Selected.Value = Blank())
)
Now, you can see it filters the records in gallery for selected country
Note that, Filter function is one of the most common used function in Power Apps, and within Filter function we frequently use operators and search functions. Few common operators such as =, <, >, IN, AND (&&), OR ( || ) etc.. and Search function such as StartsWith, EndWith, Search etc..
So, when working on large dataset you need to check whether use of any Operator or Search function within Filter function giving you a delegation warning or not.
If so, then think how you can avoid the delegation warning by modifying the formula.
To understand the delegation you can refer the post Understanding the Delegation.
Also Read..
Highlight selected item in gallery
Add Alternate background color to rows
Sort Items in gallery Power Apps
Change Item color in gallery based on value
