Filter Collection to Dropdown with Distinct Formula

(1) Share

Report

Posted on by Phineas

5,331 Moderator

I have a Dropdown that is display 'Locations' from a Collection.

I gotten it to filter, but I can't figure out how to apply the Distinct.

When I simply replace 'Filter' with 'Distinct' I break the formula.

What am I missing?

If(PJ_Job_Type_Dropdown.Selected.Value = "Drop" And varAccessLevel = "Standard",
Filter(Drop_Backup_Collection,Location, Technician_ID_Number = varUniqueID),
If(PJ_Job_Type_Dropdown.Selected.Value = "Drop" And varAccessLevel = "Administrator",
Filter(Drop_Backup_Collection,Location, Technician_ID_Number = varUniqueID),
If(PJ_Job_Type_Dropdown.Selected.Value = "Install" And varAccessLevel = "Standard",
Filter(Install_Backup_Collection,Location, Technician_ID_Number = varUniqueID),
If(PJ_Job_Type_Dropdown.Selected.Value = "Install" And varAccessLevel = "Administrator",
Filter(Install_Backup_Collection,Location, Technician_ID_Number = varUniqueID),
If(PJ_Job_Type_Dropdown.Selected.Value = "Storm Work" And varAccessLevel = "Standard",
Filter(Storm_Work_Collection,Location, Technician_ID_Number = varUniqueID),
If(PJ_Job_Type_Dropdown.Selected.Value = "Storm Work" And varAccessLevel = "Administrator",
Filter(Storm_Work_Collection,Location, Technician_ID_Number = varUniqueID),
If(PJ_Job_Type_Dropdown.Selected.Value = "Trip" And varAccessLevel = "Standard",
Filter(Trip_Project_Collection,Location, Technician_ID_Number = varUniqueID),
If(PJ_Job_Type_Dropdown.Selected.Value = "Trip" And varAccessLevel = "Administrator",
Filter(Trip_Project_Collection,Location, Technician_ID_Number = varUniqueID)))))))))   

Categories:

Building Power Apps

I have the same question (0)

All responses (4)

Answers (0)

Sort by

vipuljain03 701 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!

Distinct function returns a table with unique values from a specified column.

Syntax: Distinct(Source, ColumnName)

You can include Distinct function in your formula as shown below:

If(
PJ_Job_Type_Dropdown.Selected.Value = "Drop" And varAccessLevel = "Standard",
Distinct(Filter(Drop_Backup_Collection, Technician_ID_Number = varUniqueID), Location)

I have shown this just for 1 If condition, you can add the same logic in all If conditions.

Maybe you can also try using Switch in your formula, instead of using multiple If conditions.

----------------------------------------------------------------------------------------------------------

If this reply helped you, please mark this reply as suggested answer and give it a like to help others in the community find the answer too!

Thanks,

Vipul

Was this reply helpful? Yes No
Phineas 5,331 Moderator on at

Like (0)

Report
Copy link

Link copied!

Okay, I got it with the If statement. Still couldn't figure out how to make this a switch statement.

Lastly, the results in the dropdown do not appear in A - Z order.

How to update the below so that the results in the dropdown are sorted so that they appear in A - Z order?

If(
PJ_Job_Type_Dropdown.Selected.Value = "Trip" And varAccessLevel = "Administrator",
Distinct(Filter(Trip_Project_Collection, Technician_ID_Number = varUniqueID),Location),

PJ_Job_Type_Dropdown.Selected.Value = "Trip" And varAccessLevel = "Standard",
Distinct(Filter(Trip_Project_Collection, Technician_ID_Number = varUniqueID),Location),

PJ_Job_Type_Dropdown.Selected.Value = "Storm Work" And varAccessLevel = "Administrator",
Distinct(Filter(Storm_Work_Collection, Technician_ID_Number = varUniqueID),Location),

PJ_Job_Type_Dropdown.Selected.Value = "Storm Work" And varAccessLevel = "Standard",
Distinct(Filter(Storm_Work_Collection, Technician_ID_Number = varUniqueID),Location),

PJ_Job_Type_Dropdown.Selected.Value = "Drop" And varAccessLevel = "Administrator",
Distinct(Filter(Drop_Backup_Collection, Technician_ID_Number = varUniqueID),Location),

PJ_Job_Type_Dropdown.Selected.Value = "Drop" And varAccessLevel = "Standard",
Distinct(Filter(Drop_Backup_Collection, Technician_ID_Number = varUniqueID),Location),

PJ_Job_Type_Dropdown.Selected.Value = "Install" And varAccessLevel = "Administrator",
Distinct(Filter(Install_Backup_Collection, Technician_ID_Number = varUniqueID),Location),

PJ_Job_Type_Dropdown.Selected.Value = "Install" And varAccessLevel = "Standard",
Distinct(Filter(Install_Backup_Collection, Technician_ID_Number = varUniqueID),Location))

Was this reply helpful? Yes No
vipuljain03 701 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!
To sort the results in A-Z order, you can wrap the Distinct function with the Sort function.

Refer the below code, which also involves using With and Switch:

With( { selectedCollection: Switch( true, PJ_Job_Type_Dropdown.Selected.Value = "Drop" && varAccessLevel = "Standard", Drop_Backup_Collection, PJ_Job_Type_Dropdown.Selected.Value = "Drop" && varAccessLevel = "Administrator", Drop_Backup_Collection, PJ_Job_Type_Dropdown.Selected.Value = "Install" && varAccessLevel = "Standard", Install_Backup_Collection, PJ_Job_Type_Dropdown.Selected.Value = "Install" && varAccessLevel = "Administrator", Install_Backup_Collection, PJ_Job_Type_Dropdown.Selected.Value = "Storm Work" && varAccessLevel = "Standard", Storm_Work_Collection, PJ_Job_Type_Dropdown.Selected.Value = "Storm Work" && varAccessLevel = "Administrator", Storm_Work_Collection, PJ_Job_Type_Dropdown.Selected.Value = "Trip" && varAccessLevel = "Standard", Trip_Project_Collection, PJ_Job_Type_Dropdown.Selected.Value = "Trip" && varAccessLevel = "Administrator", Trip_Project_Collection ) }, Sort( Distinct( Filter(selectedCollection, Technician_ID_Number = varUniqueID), Location ), Result, Ascending ) )

With function is used to create a temporary context variable to store the selected collection based on the dropdown value and access level.

-----------------------------------------------------------------------------------

If this reply helped you, please mark this reply as suggested answer and give it a like to help others in the community find the answer too!

Thanks,

Vipul

Was this reply helpful? Yes No
Suggested answer

vipuljain03 701 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!

@Phineas

Please click Accept as solution if my post helped you solve your issue. ✔️ This will help others find the solution to this problem. It also closes the item.

If the content was useful in other ways, please consider giving it Thumbs Up. 👍

Was this reply helpful? Yes No