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
Dual Super User
Dual 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.

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.

 

 

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.

Helpful resources

Announcements
PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Top Solution Authors
Top Kudoed Authors
Users online (1,696)