cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ChrisChong
Advocate II
Advocate II

Help using complex filter on gallery employing SWITCH function

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

 

 

 

3 ACCEPTED SOLUTIONS

Accepted Solutions

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.

View solution in original post

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.

 

 

View solution in original post

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.

View solution in original post

10 REPLIES 10
gabibalaban
Super User
Super User

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

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.

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.

View solution in original post

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

 

Hi @ChrisChong ,

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

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

 

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.

 

 

View solution in original post

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

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.

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (2,991)