Can Power Apps SORT, DISTINCT, and FILTER functions be combined? Yes, it’s possible.
This Power Apps tutorial will explore everything about Power Apps Sort Distinct Filter with various scenarios.
Power Apps Sort Distinct Filter
Suppose you want to sort and filter the distinct values simultaneously in Power Apps. Then, in this case, we will use three Power Apps functions in the same formula: Sort, Distinct, and Filter.
We can combine all these three functions and get the result in a gallery control or a Dropdown control in Power Apps.
Refer to the scenarios below.
Example – 1:
1. I have a SharePoint list named Scheduled Events. This list has various columns with different data types. Such as:
|Title||Single line of text|
|Department||Choice [IT, HR, FINANCE, MARKETING]|
|Location||Choice [Pentagon, Decagon, Octagon, Nonagon]|
|Start Date, End Date||Date and time|
Also, this list has some records as below:
2. In Power Apps, there are two Dropdown controls where one of the dropdowns is used to select the department, and another is to display the location based on the department selection.
3. When the user selects the Department (from the first dropdown), the Location (second dropdown) will filter accordingly based on the department dropdown with distinct and sort values.
4. When I selected the MARKETING department, the location dropdown was filtered with distinct values and sorted accordingly (in ascending order).
5. To achieve this, select the Department dropdown control and apply the below code on its Items property as:
Items = Choices('Scheduled Events'.Department).Value
Department = SharePoint Choice field
6. We can see all the department choice values inside the dropdown menu once we preview the app.
7. Next, select the Location dropdown and set its Items property as:
Items = Sort( Distinct( Filter( 'Scheduled Events', Department.Value = ddSelectDepartment.Selected.Value ), Location.Value ), Result, SortOrder.Ascending )
- Department = SharePoint Choice field
- ddSelectDepartment = This is the first dropdown control name where a user can select the Department value
- Location = SharePoint Choice field
- Result = This is the second required argument in the formula.
- Ascending = Specify the order you want to display in the second dropdown control
8. Save and Preview the app. You can see the result in the location dropdown once you select the department dropdown menu.
This is how to use the Power Apps Sort Distinct Filter function.
Example – 2:
This scenario will discuss working with Power Apps Sort Distinct Filter using a SharePoint Column [Single line of text].
1. There is a SharePoint list named Job Seekers Registration List. This list has some different fields like the following:
|First Name||By default, this is a Title field with a single line of text. I just renamed it to First Name.|
|SurName||Single line of text|
|Matric Percentage, 12th Percentage, Graduation SGPA, Address||Single line of text|
This list has some below records as well:
2. In this scenario, I would like to sort the SharePoint Title field, which should be a unique value in Power Apps. Refer to the image below:
3. To do so, insert a Gallery control and apply the below formula on its Items property as:
Items = Sort( Distinct( 'Job Seekers Registration List', Title ), Result )
Title = SharePoint Title Column [Single line of text]
4. Finally, select the label that is present inside the gallery and set the below code on its Text property as:
Text = ThisItem.Result
5. Save and Preview the app. As shown above, the gallery will appear with the filtered distinct result in ascending order.
This is how to work with the sort distinct filter in Power Apps.
I hope this Power Apps tutorial will be very helpful to know everything about Power Apps Sort Distinct Filter with different examples.
Moreover, you may like some more Power Apps tutorials:
- Sort by Columns in Power Apps
- Sort Dropdown Choices Alphabetically in Power Apps
- How to Sort Gallery by Person Column in Power Apps
- How to Sort Power Apps Gallery By ID
- Power Apps Collection Distinct Filter
- Power Apps Filter Gallery By Quarter
- Power Apps Filter Gallery By Date
I am Bijay a Microsoft MVP (10 times – My MVP Profile) in SharePoint and have more than 17 years of expertise in SharePoint Online Office 365, SharePoint subscription edition, and SharePoint 2019/2016/2013. Currently working in my own venture TSInfo Technologies a SharePoint development, consulting, and training company. I also run the popular SharePoint website EnjoySharePoint.com