At PowerApps911 we have frequently been asked the question, how do I filter a gallery? In this post, we're going to make it easy for you. We will take a look at how to filter a gallery by drop-down and how to add an ‘All’ filter or blank row, using Power Apps. We include a step-by-step guide to help you create not only one but two drop-down filters, as well as a ‘Search’ function.
By using the example of a company's list of employees in each department, you will be able to create:
Distinct Filter
The ‘Distinct’ filter lists employees by different Departments within the company. For example, all the staff in Accounting are listed together under the ‘Accounting’ drop-down.
Distinct Filter Plus All
This filter lists all employees no matter what Department or job they may have, with a drop-down menu with the option to click on the different Departments. The list defaults to ‘All’. Here you will learn how to use collection and dynamically add the ‘All’ record.
Two Drop-downs
This filter allows a second drop-down of information about the employees. The example we have used is ‘Favorite Color’. For example, if you only want to see ‘Finance’ employees whose ‘Favorite Color’ is red. Side note: We do this with ‘If’ formulas and other techniques.
Two Drop-downs Plus Search
This will allow ‘Search’ functionality by first and last name.
Your Step-by-step Guide to Filtering a Gallery by Drop-down
This tutorial aims to help you get the hang of PowerApps' capabilities with regards to creating filtering by drop-downs. By using our demo app, we can explain how to do the drop-down filters. The demo app includes the four filter buttons: Distinct Filter, Distinct Filter Plus All, Two Drop-downs, and Two Drop-downs Plus Search.
Let's Get Started
Firstly, start a new screen by clicking ‘New Screen’ at the top left and click ‘Insert’ then ‘Gallery’ and choose your employees as ‘DataSource’. Type in the three fields we will be using in the example:
fx:ThisItem.’First Name’
fx:ThisItem.’Deparment’
fx:ThisItem.’Favorite Color’.Value
Our data source is SharePoint and, here, ‘Favorite Color’ is a complex column and needs .Value typed in. But just know that your data source isn't dependent on SharePoint.
Distinct Filter
To add your filter, click ‘Input’ then ‘Drop-down’.
In Properties use ‘Department’ to show all the Department data. When adding the item's property for your gallery you're going to do a filter. So, for example, if you want to run a filter of employees by Department. Type in:
fx:Filter(Employees, Department = Dropdown1.Selected.Result)
The downside here is that you may have a department listed twice.
If you have a department listed twice, you will use a function called ‘Distinct’. Type in:
fx:Distinct(Employees, Department)
It will take all employees from the table and then everything should recompile. The ‘Distinct’ function doesn't return the whole record; it just returns a special column named ‘Results’, which, in this example, has the value of ‘Department’.
Take note: The ‘Distinct’ function is not delegable. (Not familiar with this concept? We have a video on PowerApps Delegation, click here to find out more.) This means the data source can't do the work for us - finding all the ‘Distinct’ values - so by default PowerApps works with the first 500 records from the data source and then looks for the ‘Distinct’ values in there. So the ‘Distinct’ function wouldn't work very well on a large list. But you do have a lot of flexibility to hard code if necessary. Your drop-down doesn't have to use ‘Distinct’.
Another note: If you had a separate data source with a list of valid Departments you could feed that information in because our filter function matches text. So it doesn't have to be a table.
Distinct Filter Plus All
When you want to add an ‘All’ function to see all the records, an easy way to do this is to build a collection. The way to do this is by clicking ‘Screens’ and then ‘OnVisible’. By typing ‘ClearCollect you build a fresh collection. Call the collection ‘CollectVideo’; it's where you will store the ‘Distinct’ employees's Departments. Type in:
Fx:ClearCollect(collectVideo, Distinct(Employees, Department))
This is the first step in the puzzle; the ability to give us all the records in a collection.
In order to wipe out the collection and then load the first record as ‘All’. Type in this fx formula above the previous one:
Fx:ClearCollect(collectVideo, (Result: “All”});
Now you will see that ‘All’ as well as all the ‘Departments’ are listed - like you have had every other time. Although the drop-down ‘All’ is showing nothing at the moment.
To make the ‘All’ function work, you must do an ‘If’ formula. Type in:
Fx:If(Dropdown1.Selected.Result = “All”, Employees, Filter(Employees, Department = Dropdown1.Selected.Result))
6. So if All isn't chosen, it was another ‘Department’ that was chosen. You would then filter the ‘Results’ by what was chosen. The ‘If’ formula allows us to go that extra step.
Side note: Instead of using the word ‘All’ you can use dashes or leave it blank. And, you can set the default in Screens for your drop-down to ‘All’.
Two Drop-Downs
What you need to do to filter off of two is to add a second drop-down first - by clicking Input, Drop-down. Type in:
Fx:Distinct(Employees, ‘Favorite Color’.Value)
You will then add a filter on the ‘Favorite Color’ value. You'll see all the colors listed in the drop-down. Do this in the drop-down first to make sure that's the right way, and you can see what you want - because, remember, this is a complex SharePoint column that uses .Value.
Next, go back to ‘OnVisible’ to make a new ‘Collection’ and paste in the formula that returns the correct data. Type in:
Fx:Collect(collectVideo, Distinct(Employees, Department)); Fx:ClearCollect(collecVideoColors, {Result: “All}); Fx:Collect(collectVideoColors, Distinct(Employees, ‘Favorite Color’.Value))
Leave and return to the Screen and change ‘Distinct’ to ‘CollectVideoColors’.
Now things get fancy. Instead of having a simple ‘If’ scenario you will need four different scenarios for the two drop-downs. The ‘If’ formulas aren't as simple here.
First scenario:
Fx:If(ddDepartment.Selected.Result = “All” And ddColor.Selected.Result = “All”, Employees,
So, if both of these are true, then you want to show the entire employees list. And you can see if it works.
Second scenario:
ddDepartments.Selected.Result = “All” And ddColor.Selected.Result <> “All”, Filter (Employees, ‘Favorite Color’.Value = ddColor.Selected.Result.),
We use the symbol <> here for ‘does not equal’. Now you will see that 'All and All' work, and 'All and a color' work.
Third scenario:
ddDepartment.Selected.Result <> “All” And ddColor.Selected.Result = “All”, Filter(Employees, Department = ddDepartment.Selected.Result),
Now this is the reverse of the second scenario. So you copy the formula and change it by adding 'not equal to All' and 'is equal to All' and filter employees by department.
Fourth scenario:
ddDepartment.Selected.Result <> “All” And ddColor.Selected.Result <> “All”, Filter(Employees, Department = ddDeparment.Selected.Result And ‘Favorite Color’.Value = ddColor.Selected.Result)
For the final one, you paste in the formula and change 'not equal to All' and 'is not equal to All'. Filter employees by Favorite Color.
Tip: Add comments above the code to show what is for 'All and All', 'All Departments and Selected Color', 'Selected Department and All Colors', 'Selected Departments and the selected color'. This will make things easier for you.
Another Tip: Build your system in the same exact order, in baby steps. Get one step to work and then move to the next and the next, and so on. This way, you won't have to go back very far if you need to fix something.
Two Drop-Downs Plus Search
If you want the ability to search, you need to ‘Insert’ a ‘Text Input’. In Properties, change the ‘Default Value’ to be nothing and the ‘HintText’ to be ‘Search First and Last Name’. You can add a little search icon or grey text, whatever you want.
‘Search’ requires a table of data, and the function you have just built returns a table of data. So at the top of your table of data type in:
fx:Search(
And then add the text you want to ‘Search’ at the end of your table - remember to end your table of data with a closed bracket and comma. Then type in:
fx:TextInput2.Text, “FirstName”, “LastName”)
4. ‘Search’ only works with text columns. And you can still use your refiners within the ‘Search’ list.
Tip: For large data sources, find a way to refine your data before you run into problems. For example, instead of searching for all purchase orders in a big company, rather get into a filtered subset before applying refiners.
Why Use PowerApps?
If you're looking for an app that lets you build seamless and professional applications for your business, PowerApps is your best bet. PowerApps can help you easily streamlines processes and can solve challenges your organization may be facing. Plus, PowerApps lets you customize and optimize every detail of your app for specific tasks and roles.
Whatever your requirements, PowerApps let you build all the business applications you need. Whether you need assistance with an issue or complete project services, PowerApps is here to help. To watch the full video tutorial on how to How To Filter a Gallery by Drop-down by Using PowerApps, click here.