Filtering a drop-down menu in Power Apps

Display only certain results in a drop-down menu using a filter

Tutoverse - Microsoft 365

Nov 28, 2023

Topics covered in this tutorial:

• The Distinct formula to remove duplicates

• The Filter formula

• Filtering a dropdown menu based on the value of another dropdown menu

Welcome to this tutorial that will teach you how to filter a dropdown menu in your apps.

Let's start without delay by adding a button. This isn't directly related to our dropdown menus, but it will allow us to have data to work with later.

In the OnSelect property of this button, paste the following formula:

ClearCollect(ColData,{Country:"France",Player:"Mbappé"},{Country:"France",Player:"Maignan"},{Country:"France",Player:"Griezmann"},{Country:"Portugal",Player:"Ronaldo"},{Country:"Portugal",Player:"Silva"},{Country:"Argentina",Player:"Messi"},{Country:"Argentina",Player:"Di Maria"},{Country:"Croatia",Player:"Modric"})

This formula creates a collection with football players and their respective countries, with two columns:

Country: The player's country
Player: The player's name

Simple.

Hold down the Alt key and click on the button we just created (or preview the app with F5 and click on it).

In the right menu, click on (x), then Collections. Select the three dots to the right of our collection ColData and then View Table.

Here is the data source we will use. We want to be able to select the name of the country in a dropdown menu and filter to only have the players associated with that country in another dropdown menu.

Close the preview of your collection and then add a first dropdown menu.

In its Items property, add the name of the collection, namely:

ColData

Before going any further, rename your dropdown menu to DrpCountry (the contraction of Dropdown Country) for example.

Now, if you preview your app by pressing F5 and click on the dropdown menu, you will notice a problem: there are duplicates in the country choices.

This is where the Distinct formula comes in!

Go back to the Items property of the dropdown menu and add the following formula:

Distinct(ColData, Country)

Breaking down the formula, here's what we have:

We start the formula with Distinct(, asking Power Apps to distinguish elements to have only unique records.
We provide a data source, here our collection ColData.
A comma to move on to the next step.
We add the name of the column for which we want unique values, here Country.
We close the parenthesis.

If you preview your app again, you will see that the duplicates have indeed disappeared!

Now, add a second dropdown menu next to the first one.

Rename this new dropdown menu to DrpPlayer.

If we simply write ColData in its Items property, it will return the country choices again.

But how do we display the players?

You need to go to the right menu and select Player in the Value field like this:

This time, the players appear correctly in the dropdown menu choices.

We just need to filter these choices so that the available players correspond to the country we selected in the other dropdown menu.

To do this, go to the Items property of the dropdown menu containing the players and write the following formula:

Filter(ColData, Country = DrpCountry.Selected.Value)

Breaking down the formula:

Filter( starts the formula: we want to filter a data source.
We add a comma to move on to the next step.
We want the Country column to be filtered so that it matches the country we chose in the DrpCountry dropdown menu.
We tell Power Apps: the values in the Country column must be identical to the value I selected in DrpCountry.
As it is a Dropdown type item, we add .Selected.Value so that Power Apps retrieves the selected value.
We close the parenthesis.