cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Highlighted

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

Highlighted

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

Highlighted

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
Highlighted
Memorable Member
Memorable Member

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

Highlighted

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.

Highlighted

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

Highlighted

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

 

Highlighted

Hi @ChrisChong ,

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

Highlighted

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

 

Highlighted

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

Highlighted

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

Highlighted

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
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Community Highlights

Community Highlights

Check out the Power Platform Community Highlights

Top Solution Authors
Top Kudoed Authors
Users online (9,364)