How to Filter Power Apps Dropdown Control - Enjoy SharePoint (2024)

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).

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:

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.

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)

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:

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")

See Also

How to set the dropdown value and display nameCreate a cascading list boxInsert a drop-down list boxCascading Menus

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().

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:

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.

• 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:

• 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.

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.

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

• 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.

• 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.

• 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.

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)

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:

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 dropdownItems = Distinct(RevenueTable,Zone) //To display the Zone's unique value in the second dropdown

• 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.

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:

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.

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

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 controlItems = Events.Title // It will display the list of event titles within the second dropdown control

• 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.

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:

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:

• 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)

• 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.

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).

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

Also, you may like the below Power Apps tutorials:

• How to use Power Apps Combo Box Control?
• How to Reset Power Apps Combo Box
• Power Apps Cascading Dropdown Control
• Power Apps Gallery Patch [With Various Examples]
• Power Apps Gallery SharePoint[With 15+ Examples]
• Power Apps Gallery Filter [With Real Examples]
• Power Apps Gallery Control [Complete Tutorial]
• Power Apps Data Table [Complete Guide]

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