web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

Welcome to the Power Platform Communities
News and Announcements icon
Community site session details

Community site session details

Session Id :
Power Platform Community / Forums / Power Apps / Help using complex fil...
Power Apps
Answered

Help using complex filter on gallery employing SWITCH function

(1) ShareShare
ReportReport
Posted on by ChrisChong

Please fellow PowerApps App creators:
I am having a devil of a time grasping how to filter a gallery of items using several criteria in the filter.

Here is what I am trying to accomplish:

Gallery.Items

Show All Items

dd.Selected.Result = "All Cities" && ddCompleted.Selected.Value = "All"

 

Completed in all cities

dd.Selected.Result = "All Cities" && ddCompleted.Selected.Value = "Completed"

 

Not completed in all cities

dd.Selected.Result = "All Cities" && ddCompleted.Selected.Value = "Not Completed"

 

All items in selected city

dd.Selected.Result = [selected city] && ddCompleted.Selected.Value = "All"

 

Completed items in selected city

dd.Selected.Result = [selected city] && ddCompleted.Selected.Value = "Completed"

 

Not Completed items in selected city

dd.Selected.Result = [selected city] && ddCompleted.Selected.Value = "Not Completed"

 

 

If(
 DropdownCities.Selected.Result = "- All Cities -" && ddCompleted.Selected.Value = "All", SortByColumns(colSchools,"Title",Ascending),
 If(DropdownCities.Selected.Result = "- All Cities -" && ddCompleted.Selected.Value = "Completed", Filter(colSchools,Completed=true),
 If(DropdownCities.Selected.Result = "- All Cities -" && ddCompleted.Selected.Value = "Not Completed", Filter(colSchools,Completed=Blank()),
 Filter(colSchools, CITY = DropdownCities.Selected.Result)
)))

 

 

The above code sort of works. I would like to know how to employ SWITCH function to accomplish above as there are three other criteria to include in the form of dropdown boxes and a search box.

 

Thank you for your ideas/help.

Chris

 

 

 

Categories:
Building Power Apps
  • CU-18081211-6 Profile Picture
    CU-18081211-6 9,272 Moderator on at

    Hi @ChrisChong 

     

    This is not a situation suitable for the use of SWITCH function. Use the SWITCH function to evaluate a single condition against multiple possible matches. Or in your case the base conditions is different.

    However, it is not much, but for a better readability of your code, you don't have to use nested If clause.

    If(
    DropdownCities.Selected.Result = "- All Cities -" && ddCompleted.Selected.Value = "All", SortByColumns(colSchools,"Title",Ascending),
    If(DropdownCities.Selected.Result = "- All Cities -" && ddCompleted.Selected.Value = "Completed", Filter(colSchools,Completed=true),
    If(DropdownCities.Selected.Result = "- All Cities -" && ddCompleted.Selected.Value = "Not Completed", Filter(colSchools,Completed=Blank()),
    Filter(colSchools, CITY = DropdownCities.Selected.Result)
    )))

     

    Please read this article:

    https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-if

  • WarrenBelz Profile Picture
    WarrenBelz 156,754 Most Valuable Professional on at

    Hi @ChrisChong ,

    See the below - the one issue is the "Completed" field type. If a Boolean (Yes/No), you need true/false. If Text, you need "true" and Blank(). The below uses true/false

    With(
 {
 wResult:DropdownCities.Selected.Result,
 wComplete:ddCompleted.Selected.Value
 },
 SortByColumns(
 Filter(
 colSchools,
 If(
 wResult = "- All Cities -",
 true,
 CITY=wResult
 ) &&
 Switch(
 wComplete,
 "Completed",
 Completed=true,
 "Not Completed",
 Completed=false
 )
 ),
 "Title"
 )
)

     

    Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

  • Verified answer
    WarrenBelz Profile Picture
    WarrenBelz 156,754 Most Valuable Professional on at

    Hi @ChrisChong ,

    Just checking if you got the result you were looking for on this thread. Happy to help further if not.

    Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

  • ChrisChong Profile Picture
    ChrisChong on at

    I thank you for your help. Most instructive. I thought I understood how using the WITH function worked and could now expand it. However, clearly I am missing something:

     With(
 {
 wResult: DropdownCities.Selected.Result,
 wComplete: ddCompleted.Selected.Value,
 wAssigned: ddAssigned.Selected.Value
 },
 SortByColumns(
 Filter(
 colSchools,
 If(
 wResult = "- All Cities -", true, CITY = wResult,
 wAssigned = "Unassigned", true, true, PAS = wAssigned
 ) && Switch(
 wComplete, 
 "All", true,
 "Completed", Completed = true,
 "Not Completed", Completed = Blank()
 )
 ),
 "Title"
 )
 ),

    My bit of code patterned after yours does not work. Can you point it out please?

    Thanks again.

    Chris

     

  • WarrenBelz Profile Picture
    WarrenBelz 156,754 Most Valuable Professional on at

    Hi @ChrisChong ,

    You have two true statements after your second filter - you only need one.

  • ChrisChong Profile Picture
    ChrisChong on at

    Yes, I know. That was merely another feeble attempt. It did not work with only one "true" either; It throws an "Invalid argument type" error at the "=".

    Chris

    ChrisChong_0-1604036120323.png

     

  • Verified answer
    WarrenBelz Profile Picture
    WarrenBelz 156,754 Most Valuable Professional on at

    Thanks @ChrisChong ,

    What type of field is PAS in your data? If a Choice or Lookup, you need PAS.Value

     

    Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

     

     

  • ChrisChong Profile Picture
    ChrisChong on at

    Thank you, again.

    Of course, the logic is cockeyed now. 

    • PAS.Value = "Unassigned" and DropdownCities = "- All Cities -" works, that is all cites are displayed in the gallery as expected.
    • PAS.Value = "SomeName" and DropdownCities = "SomeCity"  does not work, that is the gallery does not display the items filtered by "SomeCity".

    Will I have to redo the whole solution?

    Chris

  • Verified answer
    WarrenBelz Profile Picture
    WarrenBelz 156,754 Most Valuable Professional on at

    Thanks @ChrisChong ,

    Going back a small basic step, try this

    With(
 {
 wResult: DropdownCities.Selected.Result,
 wComplete: ddCompleted.Selected.Value,
 wAssigned: ddAssigned.Selected.Value
 },
 SortByColumns(
 Filter(
 colSchools,
 If(
 wResult = "- All Cities -", 
 true, 
 CITY = wResult
 ) && 
 If(
 wAssigned = "Unassigned", 
 true, 
 PAS.Value = wAssigned
 ) && 
 Switch(
 wComplete, 
 "All", true,
 "Completed", Completed = true,
 "Not Completed", Completed = Blank()
 )
 ),
 "Title"
 )
)

     

    Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

  • ChrisChong Profile Picture
    ChrisChong on at

    Thank you. Pure genius.  I understand why you are designated "Super User"!

    Now on to the rest of my challenging App.

     

    Chris

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Welcome to the Power Platform Communities

Quick Links

Season of Sharing Community Challenge Winners!

Congratulations to our community stars!

Kudos to our 2025 Community Spotlight Honorees

Expanding mentorship, skilling, and AI innovation

Congratulations to the September Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Power Apps

#1
11manish Profile Picture

11manish 342 Super User 2026 Season 2

#2
M Bilal Khan Profile Picture

M Bilal Khan 271

#3
Mohsin Ali Profile Picture

Mohsin Ali 223

Last 30 days Overall leaderboard

Featured topics

Auto-Populate field Power Pages
Gallery Tabs - Show or Hide Tabs based on Field Value
How to patch multiple records at once
Enable Copilot for end users in model-driven apps
Question for everyone : How are you using Create Text GPT in AI Builder?
Community content - sample components, blogs etc. - Link to this page (http://aka.ms/PCFdemos)
Welcome to the Power Apps forum!

Product updates

Microsoft Power Platform Community release plans