In this tutorial, we’re going to talk about the filter function in Power Apps.
The filter function is one of the most useful functions in the platform especially if you have a lot of data to deal with. It’s very similar to the search function, which we discussed in a previous tutorial. But we get the best results if we get them to work hand in hand in our app.
How The Filter Function Works
To see how the filter function works, let’s go back to how the search function works first.
The search function basically takes a table, a search string, and the columns where you want to search.
A filter function does the same thing and turns it up to a next level.
In the Microsoft document that details what the filter function is all about, it says that Filter takes a table where it will conduct a search, and also takes formulas that evaluate each record within that table.
To understand what that means, let’s take a look at our data source.
So if you use the search function, all that it does is it takes that specific search term and checks the given columns in this table if there’s a match. So if you look for Sonya, it finds an exact match and displays that in the gallery. In this case, it will return this record.
When it comes to the filter function, it takes a conditional logic and checks it against each row in the table. If it finds a record that meets the conditions given, it returns that record.
One of the best ways to use the filter function is by setting conditions where it’s possible to have several matches across the data source. To do that, we have to create a dropdown that allows us to do that.
Adding A Dropdown Filter
For this example, let’s allow users of the app to filter records based on the VIP level. The VIP levels we have are 1, 2, 3, 4, and 5.
We can add a dropdown by clicking on Input under the Insert ribbon.
The default dropdown seems to be too big for the space we have. The good news is as with any other Microsoft platform, elements like this can be easily resized by clicking on the dots on the corners.
We can also change the colors so that it corresponds to the theme that we’re using.
Now, every dropdown has a list of items in it.
To change the items in the dropdown, all you need to do is go to the Items property and type in all the items inside square brackets. For example, you can use the words hi, hello, and bye.
Once you click Enter, you’ll see those words in the dropdown list.
This time let’s go for the actual items that we want to appear in our app. Aside from levels 1, 2, 3, 4, and 5, we also want an option to choose all of the items. So let’s start with All and put the VIP levels after that.
Once we hit Enter, the items in the dropdown menu will now show the items we added.
Using The Filter Function
If we choose an entry in our dropdown, you’ll see that it doesn’t do anything just yet.
That’s because we haven’t applied the Filter function to our app yet.
So if we choose 1 from the dropdown, we want the items in our gallery to only show items that are under VIP level 1. This means that we need to change this formula so that it performs that action.
The cool thing about Power Apps is that you can layer functions on top of functions. This means that we don’t have to get rid of the existing Search formula here. Instead, we can incorporate our filter function to this existing formula.
Our search function already runs through Table1 as it looks for the search term typed in the search field. That’s represented by TextInput1.Text. Then, it looks for that search term in the columns FirstName, LastName and AgentName.
If you’ve worked with Excel before, you’ll notice that using Filter on Power Apps is done the same way.
We add Filter at the start of the formula, then we’ll use the Search formula as the first argument.
The first argument that Filter normally looks for is a source or a table, which makes sense in this case because it will filter the same table we’re using for our search filter after it’s done looking for the specific search term.
Once we have the source, it’s time to add the conditional logic or the formula. As a condition, we want the VIP level that matched whatever is selected from the dropdown menu. If we check on the elements on the left pane, it shows that we’re using Dropdown2.
So we’ll use VIPLevel = Dropdown2.Selected.Value. Basically, this formula looks at the search output and filters it based on the selected value under the VIP Level dropdown.
So if we choose 2 on the dropdown, it filters the entire gallery and only shows entries under VIP level 2.
Let’s see if it actually works by checking on our data source. The first name in our gallery is Daine Zamora. If we check that against the table, we’ll see that Daine Zamora really is part of VIP level 2.
Adding All To The Filter Function
Although our dropdown works for VIP levels 1 through 5, if we choose All, it doesn’t return any result at all.
That’s because based on the logic we created, it’s only looking for VIP levels 1 to 5. There’s no VIP level under this column that says All.
To fix this problem, we’re going to add an IF statement that says if the user selects All from the dropdown, then there’s no need to apply this logic. If the user selects anything else other than All, then this logic will still apply.
So our IF statement will go If(Dropdown2.Selected.Value – “All”, true. This means that we want the conditional logic to return true if All is selected. Then, we’ll add VIPLevel = Dropdown2.Selected.The value so that it goes through the usual filtering process if anything other than All is selected.
If we try that out and choose All on our dropdown, you’ll see that it displays all items in our gallery.
But if we choose 1 from the dropdown, it automatically filters the whole gallery and now only shows the entries under VIP level 1.
Now, let’s check if both the search and filter functions will work at the same time.
With 1 chosen from the dropdown, let’s type Zamora in the search bar. As you can see, it doesn’t return any results.
That’s because we’re not even sure if Zamora falls under VIP level 1. So let’s try that same search term under All.
Now, it’s showing Daine Zamora, which means that both the filter and the search functions are working perfectly.
***** Related Links *****
Power Apps Introduction: Definition, Features, Functions And Importance
Power Apps Environments: Setting Up The App Elements Properly
Power Apps Canvas: How To Create An App From Scratch
Conclusion
The filter function definitely takes our app to a whole new level, especially in terms of ease of use. Now, users won’t have to scroll through all the names in our gallery. All they need to do is type in a search term and filter it accordingly.
What we saw here is just the beginning. It’s possible to have multiple filters on a single screen, as well as multiple search bars. It all depends on the amount of data you have and the ways that you can group records together.
All the best,
Henry