Power Apps Filter Gallery With Multiple Dropdowns

Power Apps Filter Gallery With Multiple Dropdowns

Filters are an important part of every Power Apps gallery design. They enable the user to select a few criteria and quickly show matching results on the screen. Knowing how to filter in a delegation-friendly way is important because its the only way to check an entire SharePoint list. By comparison the Search has will not return any results past the 2,000 row delegation limit.

In this article I will show you how to filter a Power Apps gallery with multiple dropdowns including an all option.

Table Of Contents:
Introduction: The Paid Time-Off App
Setup The SharePoint List
Creating a Gallery And Multiple Dropdowns
Adding Dropdown Values 
Filtering The Gallery By Multiple Dropdowns




Introduction: The Paid Time-Off App

The Paid Time-Off App is used by employees of a company to request a day-off from work. Managers can view the details of all requests made by their employees.





Setup The SharePoint List


Create a new SharePoint list called Paid Time Off and include the following columns:

  • TimeOffDate (date only)
  • TimeOffType (single-line text),
  • Status (single-line text)
  • Employee (single-line text)

Populate the SharePoint list with this sample data:

TimeOffDateEmployeeTimeOffTypeStatus
9/1/2020Kelly SmithPersonalSubmitted
9/4/2020Kelly SmithVacationApproved
9/5/2020Kelly SmithVacationApproved
9/8/2020Jennifer HoustonVacationRejected
9/12/2020Sarah GreenPersonalSubmitted
9/13/2020Jennifer HoustonVacationApproved
9/15/2020Sarah GreenPersonalRejected
9/18/2020Kelly SmithPersonalRejected
9/19/2020Sarah GreenSickApproved
9/21/2020Jennifer HoustonPersonalSubmitted



Open Power Apps Studio and create a new blank canvas app. Add a connection to the ‘Paid Time Off’ SharePoint list. Then insert a gallery onto the screen with ‘Paid Time Off’ as the datasource and display date, employee, time-off type and status in it.




Position a set of dropdowns to-the-right of the gallery. Give each dropdown a name: Type (drp_Type), Status (drp_Status) and Employee (drp_Employee). Once finished your app should look like the image shown below.




Adding Dropdown Values

Now that the app-design is complete we will focus on loading the dropdown menus with values. The Type dropdown has 4 options as shown below including a blank value in the top position.



There are only 3 possible Types so we can use this code in the Items property of the Type dropdown to populate it with values

[Blank(), "Personal", "Sick", "Vacation"]



The Status dropdown also has 4 choices.



Similarly, there are 3 possible Statuses included in the app-design so hardcode them into the Items property of Status dropdown as well.

[Blank(), "Submitted", "Approved", "Rejected"]



Currently the Employee dropdown shows 3 names but the company might add more people in the future.



Since the employees list will grow over time we can use this code in the Items property of the dropdown to show the unique names found in the Employee column of the SharePoint list. A clever reader of my blog named Ed Hansberry shared originally this method of adding a blank row to the top of a dropdown on Twitter.

Ungroup(
    Table(
        {MyTables: Table({Result: Blank()})},
        {MyTables: RenameColumns(
            Distinct('Paid Time Off', Employee),
            "Result", "Value")
        }
    ),
    "MyTables"
)



Please note that because the employee dropdown code includes the DISTINCT function it is not-delegation friendly. If your SharePoint list will be greater than 2,000 rows I would suggest using this method to change the Employee data type to Person and use a People Picker ComboBox instead.

All of the dropdowns menus now have options when you click them.

The final step is to filter the gallery by the current values selected in each dropdown as shown below.



We can accomplish this by using a single FILTER function in the Items property of the gallery. This coding pattern is delegation-friendly. When a dropdown has a value we filter the gallery based on the selection, otherwise, if the dropdown has a blank value we show all options for that column.

Filter(
    'Paid Time Off',
    drp_Type.Selected.Value=Blank() Or TimeOffType=drp_Type.Selected.Value,
    drp_Status.Selected.Value=Blank() Or Status=drp_Status.Selected.Value,
    drp_Employee.Selected.Value=Blank() Or Employee=drp_Employee.Selected.Value
)



Want To Read The Next Article In This Series?

My next article will show you how to add a Sharepoint-style filter menu with apply, clear and reset controls. Click the link to read Power Apps Filter Menu: Apply, Clear and Reset





Questions?

If you have any questions or feedback about Power Apps Filter Gallery With Multiple Dropdown please leave a message in the comments section below. You can post using your email address and are not required to create an account to join the discussion.

Matthew Devaney

Subscribe
Notify of
guest
16 Comments
Oldest
Newest
Inline Feedbacks
View all comments
Teresa Agustin
Teresa Agustin
2 years ago

Hello Matthew,

Thank you for writing this article. I really enjoy all of your blog posts, they are so helpful! As you know, I recently implemented your sorting method with a kind assist from yourself when it came to integrating it into my existing gallery items formula. I’m currently trying to filter my gallery by three different column types- Requestor, Program Manager and Mechanical Engineer. I’m trying to follow the concepts outlined in Reza’s video for filtering by people columns (it’s a filter by logged in user concept), but since I’m new and it doesn’t exactly fit my use case of multiple dropdowns such as you have shown here, I’m having trouble translating it. Would you be able to do a follow-up blog post to this one on filtering by people columns? Unfortunately, it isn’t really feasible for me to use a Text column method. I tried once using Laura Roger’s add columns method, but had trouble adding new items to the gallery with my new request form once it was in place so I had to abandon that approach. Maybe I just wasn’t doing it right? Anyway, here are links to both Reza’s and Laura’s tutorials. When I asked Reza how to translate the guidance in his video for my use case, he recommended using the same concept to create 3 options to choose people from and then set those in variables and query them from the gallery. Since I am still new to PowerApps, I’m not really sure how to go about this. I’m still at the point where I need step-by-step tutorials. Thanks for considering writing a blog on this topic.

Reza DorraniPart 5 – PowerApps Delegation & Gallery Filtering using Person column (single and multi select)https://www.youtube.com/watch?v=tPxE_-DcWkg

Laura Rogers
SharePoint Power Hour: PowerApps Custom Filteringhttps://www.youtube.com/watch?v=5wfSAsU93b4
Laura selects dropdown values and then clicks a funnel icon to filter the gallery. I prefer your method where no separate icon click is required.

Kind regards,
Teresa

Teresa Agustin
Teresa Agustin
2 years ago

Thanks for the quick reply Matthew. Please forgive the length of this post, but I want to try to describe my situation in enough detail to be helpful in hitting the mark.

Each project only has 1 of each of the respective roles – Requestor, Project Manager and Mechanical Engineer so would a dropdown control be sufficient? I realize I am not your only audience member so if you want to show a combo box for at least one of the options for the sake of education, I could adjust my approach according to the option that seems to fit my situation.

I capture the requestor OnStart of the app with a global variable –   Set(varUser,User()).

The Project Manager name auto-populates based off the project number they select from a separate Project List, but I don’t think this makes a difference since at the end of the day, the name ends up in a person column within the primary list that is being displayed in the home screen gallery.

I have a People Picker control in an edit form for the sake of manually selecting the Mechanical Engineer that will be assigned to the project.

The only other thing I would mention is that you also helped me with a formula that abbreviates the gallery names to LastName.FirstInitial (e.g. Devaney M.). Not sure if this makes a difference to the drop downs or if you could just show the drop down as a full display name. I would prefer the full Display Name if possible.

Here are the gallery label Text property formulas for each column. Feel free to disregard if this formatting doesn’t make any difference. The only extra consideration might be the Mechanical Engineer. As you can see, if an engineer hasn’t been assigned yet, the field reads pending. It would be nice to be able to filter on pending to see which jobs still need to have someone assigned to them.

Requestor:
With(
  {UserName: Split(ThisItem.’Created By’.DisplayName, ” “)},
  First(UserName).Result & ” ” & Left(Last(UserName).Result,1)& “.”
)

Project Manager:
With(
  {UserName: Split(ThisItem.PMPEPerson.DisplayName, ” “)},
  First(UserName).Result & ” ” & Left(Last(UserName).Result,1)& “.”
)

Mechanical Engineer:
With({dn:Match(ThisItem.’ME Assigned’.DisplayName, “^.*\s.”).FullMatch},
  If(IsBlank(dn), “pending”, dn & “.”)
)

Thanks so much for all the help you have given me. I’m afraid my first app out of the gate was a rather ambitious one, but I hope if I can master these key concepts that the others will roll out more easily. I’m sure I speak for many when I say you and other taking the time to help us is very kind and greatly appreciated!

Let me know if there is any other info you need.

Sincerely,
Teresa

Teresa Agustin
Teresa Agustin
2 years ago

Hi Matt, thank you for adding these instructions. My organization has more than 999 employees. Do you think this will prevent me from being able to use the Office365/ComboBox approach?

Charles-Antoine
Charles-Antoine
2 years ago

Hi Matthew,

I wanted to do exactly this type of filter but I was postponing it because I didn’t know how to do it. It wasn’t that obvious…
Thank you for sharing!

GFGF
GFGF
2 years ago

Hi Mat
clearly explained?
under Filtering The Gallery, how can I filter for specific date or selected date from the date picker please.
specific date means I need to filter gallery for yesterday by default or user selected date.
your thought is appreciated

Bart
2 years ago

Hi Matthew

Thanks for your great tutorials. They are real awesome!
I was struggling using different filters since a long time. Writing complex switch lines, trying to get all possible scenarios in it.
Your solution makes it so much easier! Although I haven’t tried it yet 🙂

This line:

drp_Type.Selected.Value=Blank()

does that makes that filter condition void or how should i read this?

Thanks for sharing your content!

Leigh
Leigh
1 year ago

Hi Mathew, thank you so much for this article! I just implemented these filters in my app. Your blog is so helpful and really appreciate it!! Looking forward to more of your blogs.

Iñigo N.
Iñigo N.
1 year ago

Hi Matthew,

Thank you very much for the article.It is in my favorite bookmarks and i have shared it with all my collegues. I have a question that I can’t solve

Filtering the Gallery, how can I filter at the same time by:

Dropdowns All value ( all the elements )
Dropdowns Empty value ( only empty departments )
Dropdowns Department value ( department )

your thought is appreciated,
Sincerely,
Iñigo

Last edited 1 year ago by Iñigo N.
Steve Myles
Steve Myles
8 months ago

I would like to choose which SharePoint column to filter my gallery with by using a dropdown as below

Filter(SP List, Dropdown.Selected.Value = true)

with a Boolean choice column of which there are several choices. Dropdown.Selected .Value contains the names of the column but when i try this Powerapps just ‘sees’ the text value and does not recognise it as a column reference.Is there a way to make Powerapps do this?

Jason Roberts
Jason Roberts
4 months ago

Brilliant. I can’t believe it took so long to stumble onto this. (Historically I relied on the overly-complicated series of If-Then statements — If Dropdown 1 <> “X” and Dropdown 2 = “Y” and IsBlank(Dropdown 3) then Filter … and so on. Mind-numbing.)

Your site is a great resource. 🙂