How to Filter Power Apps Dropdown Control

Do you ever wonder how to display disorganized data orderly when using a Power Apps dropdown control? If that is the case, then this Power Apps guide will be a real asset in helping you get rid of it.

  • Power Apps dropdown filter gallery
  • Power Apps dropdown filter data table
  • Power Apps dropdown filter shows all
  • Power Apps dropdown filter yes or no
  • Power Apps dropdown filter distinct
  • powerapps filter dropdown from collection
  • powerapps filter dropdown from excel table
  • Power Apps dropdown filter another dropdown
  • Power Apps filter dropdown and sort distinct

Power Apps dropdown filter gallery

Do you want to filter a Power Apps gallery based on dropdown selection? If this is the case, then recently we published an article that described how to filter a gallery using a Power Apps dropdown control. We recommend you follow that link to get the answer.

Power Apps dropdown filter data table

Here, we will see how to filter a data table based on the Power Apps dropdown selection.

Suppose, we have a SharePoint list named “vendors” with columns such as Title, date(date and time column), and status(a choice column).

Example of SharePoint list
Example of SharePoint list

Let’s connect this SharePoint list to the Power Apps. On the Power Apps, we have built a data table using the above SharePoint list like below:

powerapps dropdown filter data table
powerapps dropdown filter data table

Next, add a dropdown control to the screen and insert the below expression on the dropdown’s Items property to display the choices of the status column.

Items = Choices(vendors.Status)

Where Vendors is the name of the SharePoint data source and ‘Status‘ is the choice column name.

Filter DataTable with Power Apps dropdown
Filter DataTable with Power Apps dropdown

To filter the data table based on the dropdown selection, we will insert the below expression on the data table’s Items property.

Items = Filter(vendors,Status.Value = StatusDD.Selected.Value)

Where StatusDD is the name of the dropdown control name.

Once the formula is applied to the data table, we can see the filtered item will display like below: (by default the first item is selected in the dropdown control)

Filter Data table based on Power Apps Dropdown Value
Filter Data table based on Power Apps Dropdown Value

This is how to filter a Data table based on the Power Apps dropdown value.

Also read: How to Work With Power Apps Dropdown Blank Values

Power Apps dropdown filter shows all

If you want to work with the Power Apps dropdown filter shows all, then use the link provided to accomplish your goal. Where we used a dropdown control with an item such as ‘All‘.

When the user clicks on the ‘All‘ option, the gallery will display all of the items. Otherwise, the filtered item will be displayed based on the dropdown selection.

Power Apps dropdown filter yes or no

Here, we will see how to filter a SharePoint YesNo column using a Power Apps dropdown control within a gallery.

Suppose, we have a SharePoint list named Employees that contains a ‘Yes/No column including other columns such as the People column, and a DateTime column like below:

Power Apps dropdown filter yes or no
Power Apps dropdown filter yes or no

We created a Power Apps gallery that displays all of the above SharePoint list data. Because the SharePoint ‘yes/no’ column represents a boolean value, we used the below expression on the label’s text property to convert it to ‘yes’ and ‘no’.

Text = If(ThisItem.Active = true, "Yes", "No")
Filter based on YesNo with dropdown
Filter based on Yes/No with Power Apps dropdown

Next, add a dropdown control to the screen and insert the below expression on the dropdown’s Items property, which will display the Active column or ‘YesNo‘ column data.

Items =Distinct(Employees,Active)

To remove the duplicate values, we have used the distinct().

Power Apps Dropdown to filter based on a yes/no field
Power Apps Dropdown to filter based on a yes/no field

Now we will insert the below expression on the gallery’s Items property to filter the gallery as per the dropdown selection.

Items = Filter(Employees,Active = ActiveDD.Selected.Result)

Where ActiveDD is the name of the dropdown control. Once the formula is applied to the gallery, we can see the filtered items will display like below:

Filtering a Dropdown based on a YesNo Field In SharePoint list
Filtering a Dropdown based on a YesNo Field In the SharePoint list

This is how to filter a Dropdown based on a YesNo field in the SharePoint list.

Check out: How to Remove Duplicates in Power Apps Dropdown

Power Apps dropdown filter distinct

Here, we will see how to work with the Power Apps dropdown filter with distinct. That means when the user selects any item from the dropdown control, the items will filter as per the selected item as well as display only the unique value. To work with this requirement, the following steps are:

  • We have prepared a SharePoint list named Employees having a people column and a choice column with some repetitive data like below.
powerapps dropdown filter distinct
powerapps dropdown filter distinct
  • On the Power Apps screen, we have built a gallery to display the employees’ names and a dropdown list to display the choices of the departments like below:
Distinct filtered values on a Power Apps drop down
Distinct filtered values on a Power Apps dropdown
  • Next, to display the distinct filtered data on the above gallery based on the dropdown selection, we need to insert the below expression on the gallery’s Items property.
Items = Distinct(Filter(Employees,Departments.Value = DepartmentsDD.Selected.Value),EmployeeName)

where DepartmentsDD is the name of the dropdown control.

Filter Duplicates based on Power Apps Drop Down
Filter Duplicates based on Power Apps Drop Down

Once the formula is applied, we can see there is an error in the gallery. To get rid of that, select that error and insert the below expression on the label’s Text property.

Text = ThisItem.Result.DisplayName

Here the DisplayName is the property of the people picker column i.e., EmployeesName.

Now we can see the filtered distinct items will be visible in the gallery as per the dropdown selection.

Filter a dropdown with distinct values on Power Apps
Filter a dropdown with distinct values on Power Apps

This is how to filter a dropdown with distinct values on Power Apps.

Read: How to Set Default Value in Power Apps Dropdown

Power Apps filter dropdown from collection

Here, we will see how to work with the PowerApps filter dropdown from the collection. That means we will build a collection and filter that collection using a Power Apps dropdown control. To work with this, the following steps are:

  • On the Power Apps screen, let’s add a button control and insert the below expression on the button’s OnSelect property.
OnSelect = ClearCollect(ColVehicles,{Name: "Lexus", Manufacture: 2022},{Name: "Audi Q7", Manufacture: 2021},{Name: "Mercedes Benz", Manufacture: 2020},{Name: "BMW", Manufacture: 2021},{Name: "KIA", Manufacture: 2022},{Name: "Genesis", Manufacture: 2021})

Where,

  1. ColVehicles: The name of the collection
  2. Name, Manufacture: The name of the collection headers
PowerApps filter dropdown from collection
PowerApps filter dropdown from collection
  • Add a vertical gallery to the Power Apps screen and insert the collection name (i.e., ColVehicles) on the gallery’s Items property to display the collections.

Note: Initially, the items will not be displayed in the collection as it has not yet been created. So first, click on the Alt key + Button to create the collection, then it will automatically display the collection’s data on the gallery like below.

Filter collection by Power Apps dropdown
Filter collection by Power Apps dropdown
  • Then add a dropdown control to the above Power Apps screen, and insert the below expression on the dropdown’s Items property to display the manufacture year.
Items = Distinct(ColVehicles,Manufacture)
  • To remove the repetitive items, we have used the distinct() function on Manufacture.
Filter collection based on Power Apps dropdown selection
Filter collection based on Power Apps dropdown selection
  • Finally, we will insert the below expression on the gallery’s Items property to display the filtered data as per the dropdown selection.
Items = Filter(ColVehicles,Manufacture = ManufactureDD.Selected.Result)

Where ManufactureDD is the name of the dropdown control.

Filter collection by dropdown on Power Apps
Filter collection by dropdown on Power Apps

Let’s save and publish the app. Play the app in preview mode. When we select any manufacturer’s year, the collection gallery will be filtered according to the selection. (Ex- 2021)

Filter PowerApps dropdown from collection
Filter PowerApps dropdown from collection

This is how to filter the collection based on the Power Apps dropdown control.

Check: How to use Power Apps Gallery Dropdown

Power Apps filter dropdown from excel table

Here, we will see how to work with the PowerApps dropdown filter from the excel table.

Suppose we have an excel table like the below:

PowerApps dropdown filter from the excel table
PowerApps dropdown filter from the excel table

Let’s connect this excel table to the Power Apps and build 2 dropdowns (for month and zone). When the user selects items from both the dropdown controls, the filter data will display on a Power Apps gallery or a data table.

To work with this requirement, the following steps are:

  • To display the excel table data, add a data table to the Power Apps screen and enter the excel table name into the data table’s Items property.
Items = RevenueTable

Where RevenueTable is the name of the excel table.

  • Then insert two dropdown controls to the Power Apps screen and insert the below expressions on the dropdown’s Items property.
Items = Distinct(RevenueTable,Month)     //To display the month's unique value in the first dropdown
Items = Distinct(RevenueTable,Zone)      //To display the Zone's unique value in the second dropdown
PowerApps filter dropdown from excel table
PowerApps filter dropdown from excel table
  • To filter the data table based on the dropdown’s selections, insert the below expression on the data table’s Items property.
Items = Filter(RevenueTable,Month= MonthDD.Selected.Result Or Zone = ZoneDD.Selected.Result )

Where MonthDD and ZoneDD are the names of the dropdown controls.

Power Apps filter excel table based on dropdown
Power Apps filters excel table based on dropdown

Let’s play this app and select items from both the dropdown controls such as Apr and West.

We can see the excel data table will display only those items that contain Apr or West like below:

Simple Filter App Based on Excel Sheet using Power Apps dropdown
Simple Filter App Based on Excel Sheet using Power Apps dropdown

This is how to filter an excel sheet using Power Apps dropdown control.

Also check: Power Apps Dropdown Control with SharePoint

Power Apps dropdown filter another dropdown

Here, we will see how to filter a dropdown based on another dropdown control within the Power Apps environment. To work with this requirement, we have prepared two SharePoint lists named Events and Organizer.

In Organizer, we have inserted some companies’ names in the Title field.

powerapps dropdown filter another dropdown
powerapps dropdown filter another dropdown

Similarly, in the Events SharePoint list, we have created a lookup column named Organizer by using the above Organizer’s Title column.

PowerApps dropdown filter based on another dropdown
PowerApps dropdown filter based on another dropdown

Now, we will build two dropdown controls. From which one will display the choices of organizers and another one will display the event’s title. When we select any organizer, the respective event(s) will display on another dropdown. To work with this, the following steps are:

  • On the Power Apps screen, add two dropdown controls and insert the below expressions in the respective dropdown’s Items property.
Items = Organizer.Title       // It will display the organizer's title within the first dropdown control

Items = Events.Title         // It will display the list of event titles within the second dropdown control
filter powerapps dropdown based on another dropdown
Filter powerapps dropdown based on another dropdown
  • Next, to filter the dropdown based on another, select the second dropdown and replace the below expression within the second dropdown’s Items property. (That shows the list of events)
Items = Filter(Events,Organizer.Value = OrganizerDD.Selected.Title)

Where OrganizerDD is the name of the dropdown control.

Filter dropdown items based on another dropdown value Power Apps
Filter dropdown items based on another dropdown value Power Apps

Let’s save the app and play it in preview mode. We can see when we select any organizer, the respective event(s) will display in the event dropdown. Suppose, we have selected Apple, then the filtered data will come like below:

PowerApps filter dropdown based on another dropdown
PowerApps filter dropdown based on another dropdown

This is how to filter a dropdown based on another dropdown within the PowerApps.

Read: Power Apps Gallery Group By [With Real Examples]

Power Apps filter dropdown and distinct sort

Here, we will see how to work with the PowerApps dropdown filter and distinct sort. That means when the user selects any item from the first dropdown, the unique filtered data will display in another dropdown as well as sort the data in ascending or descending order.

To implement this need, we are going to use the above SharePoint list Event where we have added a choice column named location like below:

PowerApps dropdown filter and sort distinct
PowerApps dropdown filter and sort distinct
  • On the Power Apps screen, build a dropdown and insert the below formula on the Items property to display the list of organizers.
Items = Choices(Events.Organizer)
powerapps filter dropdown and sort distinct
powerapps filter dropdown and sort distinct
  • Add another dropdown control to the above screen and insert the below expression to display the filtered distinct value in descending or ascending order.
Items = Sort(Distinct(Filter(Events, Organizer.Value = OrganizerDropdown.Selected.Value),Location.Value), Result,Descending)

Where,

  1. OrganizerDropdown is the name of the first dropdown name that displays the list of organizers.
  2. Organizer.Value, and Location.Value is the SharePoint choice column.
filter powerapps dropdown and sort distinct
Filter powerapps dropdown and sort distinct

Let’s play the app in preview mode. Select any item from the organizer dropdown (ex-Apple). We can see the location will appear in descending order (alphabetically).

Sort and filter a Power Apps dropdown
Sort and filter a Power Apps dropdown

This is how to sort and filter a Power Apps dropdown control with distinct values.

Also, you may like the below Power Apps tutorials:

Conclusion

From this Power Apps tutorial, we learned all about how to perform filters using the Power Apps dropdown control based on different types of scenarios. such as:

  • Power Apps dropdown filter gallery
  • Power Apps dropdown filter data table
  • Power Apps dropdown filter shows all
  • Power Apps dropdown filter yes or no
  • powerapps dropdown filter distinct
  • powerapps filter dropdown from collection
  • powerapps filter dropdown from excel table
  • powerapps dropdown filter another dropdown
  • Power Apps filter dropdown and distinct sort
  • Hello,
    Thank You for good tutorial.
    I have a question. How I can to combine search in gallery (dropdown list and/or searchbox)?
    When i use this search separatly all works fine. How I can add both search to one gallery?

    Works fine from SEARCHBOX:
    SortByColumns(Search([@INW]; TextSearchBox1.Text; “NUMER”;”OPIS”;”Użytkownik”;”SERIAL”;”LOKALIZACJA”;”NAKLEJKA”); “NUMER”; If(SortDescending1; SortOrder.Descending; SortOrder.Ascending))

    Works fine from dropdown list:
    Filter([@INW];NAKLEJKA= Dropdown1.Selected.Value)

    Together don’t work. What I doing wrong?
    SortByColumns(Search([@INW]; TextSearchBox1.Text; “NUMER”;”OPIS”;”Użytkownik”;”SERIAL”;”LOKALIZACJA”;”NAKLEJKA”); “NUMER”; If(SortDescending1; SortOrder.Descending; SortOrder.Ascending)) || Filter([@INW];NAKLEJKA= Dropdown1.Selected.Value)

  • >