cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
marcy_pi
Helper III
Helper III

Filter gallery with dropdown boxes using collections

Hello,

 

I have 3 dropdown boxes that are filtering my gallery. Each of dropdown box is using collection of elements from the gallery (data source is excel). I could filter through All parts and distinct parts on 1st one, all months and distinct months on 2nd one and on 3rd one I wanted to filter through: All, value above 50 and value below 50. How do I do that, please? 

This is my code on screen: (OnVisible)

ClearCollect(colPartNumberP, {Result:"All"}, Distinct(Table5, 'PART NUMBER'));
ClearCollect(colMonthP, {Result:"All"}, Distinct(Table5, month));
ClearCollect(colValueP, {Result:"All"}, {Result:"Above £50"}, {Result:"Below £50"})

 

This is my code on dropdown boxes: 

colPartNumberP

colMonthP

colValueP

 

This is my code on gallery: 

If(
Dropdown_Part_P.Selected.Result = "All" && Dropdown_Month_P.Selected.Result = "All" && Dropdown_Value_P.Selected.Result = "All",
Table5,
If(
Dropdown_Part_P.Selected.Result <> "All" && Dropdown_Month_P.Selected.Result = "All" && Dropdown_Value_P.Selected.Result = "All",
Filter( Table5, 'PART NUMBER' = Dropdown_Part_P.Selected.Result ),
If(
Dropdown_Part_P.Selected.Result <> "All" && Dropdown_Month_P.Selected.Result <> "All" && Dropdown_Value_P.Selected.Result = "All",
Filter( Table5, 'PART NUMBER' = Dropdown_Part_P.Selected.Result && month = Dropdown_Month_P.Selected.Result),
Filter( Table5, month = Dropdown_Month_P.Selected.Result )
)
)
)

 

All collections are working fine except of collection for value as nothing is assigned to these and in gallery I have not included them as I am not sure how to filter them.

 

Regards 

1 ACCEPTED SOLUTION

Accepted Solutions

@marcy_pi ,

I cannot test it but it should work - I was trying to keep it Delegable with that structure. There is a "long way" that will work, but the top filter needs to return less than your Delegation limit to get the full results

With(
   {
      wList:
      Filter(
         Table5,
         (
            Dropdown_Part_P.Selected.Result = "All" ||
            'PART NUMBER' = Dropdown_Part_P.Selected.Result
         ) &&
         (   
            Dropdown_Month_P.Selected.Result = "All" ||
            month = Dropdown_Month_P.Selected.Result
         ) 
      )
   },
   If(
      Dropdown_Value_P.Selected.Result = "All",
      wList,
      Filter(
         wList,
         If(
            Dropdown_Value_P.Selected.Result = "Above £50",
            ValueField >= 50,
            ValueField < 50      
         )
      )
   )
)   

 

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.

Visit my blog Practical Power Apps

View solution in original post

4 REPLIES 4
WarrenBelz
Super User
Super User

Hi @marcy_pi ,

You asked this question in part on another post - try this

Filter(
   Table5,
   (
      Dropdown_Part_P.Selected.Result = "All" ||
      'PART NUMBER' = Dropdown_Part_P.Selected.Result
   ) &&
   (   
      Dropdown_Month_P.Selected.Result = "All" ||
      month = Dropdown_Month_P.Selected.Result
   ) &&
   (
      Dropdown_Value_P.Selected.Result = "All" ||
      (
         (
            Dropdown_Value_P.Selected.Result = "Above £50" && 
            ValueField >= 50
         ) ||
         ValueField < 50      
      )
   )
)   

 

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.

Visit my blog Practical Power Apps

 

marcy_pi
Helper III
Helper III

Thank You,

 

It is working for option "All" and for "Below £50" but when I press on "Above £50" it is showing me all records not only above the value. Do You know why?

@marcy_pi ,

I cannot test it but it should work - I was trying to keep it Delegable with that structure. There is a "long way" that will work, but the top filter needs to return less than your Delegation limit to get the full results

With(
   {
      wList:
      Filter(
         Table5,
         (
            Dropdown_Part_P.Selected.Result = "All" ||
            'PART NUMBER' = Dropdown_Part_P.Selected.Result
         ) &&
         (   
            Dropdown_Month_P.Selected.Result = "All" ||
            month = Dropdown_Month_P.Selected.Result
         ) 
      )
   },
   If(
      Dropdown_Value_P.Selected.Result = "All",
      wList,
      Filter(
         wList,
         If(
            Dropdown_Value_P.Selected.Result = "Above £50",
            ValueField >= 50,
            ValueField < 50      
         )
      )
   )
)   

 

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.

Visit my blog Practical Power Apps

marcy_pi
Helper III
Helper III

You're genius! 😄 Thanks!

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Difinity Conference 2022

Difinity Conference 2022

Register today for two amazing days of learning, featuring intensive learning sessions across multiple tracks, led by engaging and dynamic experts.

European SharePoint Conference

European SharePoint Conference

The European SharePoint Conference returns live and in-person November 28-December 1 with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Power Apps Ideas

Changes to Ideas Coming

We are excited to announce a new way to share your ideas for Power Apps!

Top Solution Authors
Top Kudoed Authors
Users online (3,004)