cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Au_secours
Regular Visitor

How to filter gallery with dropdown through search bar

Hi, I need some help filtering my gallery color with my dropdown through the search bar. I haven't found any recourses that aid towards this issue, so I thought I'll create one here.

 

Below are the screenshots and formulae 

 

Dropdown1 ;Items = Table11

Au_secours_2-1626716272296.png

 

Gallery2 ;Items = Search(CAOProjectPortfolio,txtSearchProj.Text,"ProjectName","ProjectManager","Category") 

Au_secours_1-1626714760561.png

I had to hide the name and project ID, Project manager, and category.

 

So, I am trying Filter colors by Table11 values

- Completed= Blue, white

- Active= Green, Yellow

- Not Started = Gray 

- Stopped = Red

 

Here are the formulas on the color circle.

Circle3 ;OnSelect = Select(Parent)

           ;Fill = Switch(ThisItem.'OverallStatus(CW)',"Not Started / On Hold",LightGray,"On Track",Green,"Delayed",Orange,"At Risk / Stopped",Red,"Complete",Blue,White)

 

Thank you and let me know if more information is needed to help me with this task.

 

 

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @WarrenBelz Thanks for checking. I apologize for the late response. 

Below is my formula I placed in Gallery2 Items;

 

Search( 
        If('Dropdown1'.Selected.Result ="All", 'CAOProjectPortfolio', 
         'Dropdown1'.Selected.Result <>"All", 
       Filter('CAOProjectPortfolio', 'OverallStatus(CW)' = 'Dropdown1'.Selected.Result), 
         'Dropdown1'.Selected.Result ="All",'CAOProjectPortfolio',
       Filter( 
            'CAOProjectPortfolio','OverallStatus(CW)' = 'Dropdown1'.Selected.Result)), 
      txtSearchProj.Text,"ProjectName","ProjectManager","Category")

 

I also made a formula for 'OnVisible' which helped

 

ClearCollect(Table11, {Result: "All"});
Collect(Table11, Distinct('CAOProjectPortfolio','OverallStatus(CW)'))

 

And with all of these, I was able to filter the gallery colors using my dropdown.

View solution in original post

8 REPLIES 8
WarrenBelz
Super User III
Super User III

Hi @Au_secours ,

I am a little uncertain here what is wrong with your Fill code (or what exactly you are trying to do)

Switch(
   ThisItem.'OverallStatus(CW)',
   "Not Started / On Hold",
   LightGray,
   "On Track",
   Green,
   "Delayed",
   Orange,
   "At Risk / Stopped",
   Red,
   "Complete",
   Blue,
   White
)

This will certainly colour your circle as per the value of the 'OverallStatus(CW)' field in the gallery. What is different between these and the Table11 dropdown values?

 

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 @WarrenBelz Thanks a lot for replying. I am trying to use the dropdown Table11 to filter the gallery.  The difference is that Table11 has been grouped (Into Completed, active, Not Started, and Stopped) whereas, 'OverallStatus(CW)' consist of all the folders as they appear in the gallery.

 

So I am looking for a formula to place in Dropdown1 and an updated formula for gallery2 if needed. Such that if I click on Completed in my dropdown, I should be able to only see the folders with Blue and white circle.

 

Thank you!

@Au_secours ,

What is the field name in Table11 which appears in Dropdown1 and what is the name of the field in your gallery that matches this?

@WarrenBelz 

 The field name for Table11 is 'Stus Value' and the name of field in my gallery that goes with it is CAOProjectPortfolio

@Au_secours ,

CAOProjectPortfolio is the List name, not the Field name (according to your Gallery Items posted) - you should need in the Gallery Items

Search(
   Filter(   
      CAOProjectPortfolio,
      YourFieldName = DropDown1.Selected.'Stus Value'
   ),
   txtSearchProj.Text,
   "ProjectName",
   "ProjectManager",
   "Category"
) 

 

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.

 

WarrenBelz
Super User III
Super User III

Hi @Au_secours ,

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.

Hi @WarrenBelz Thanks for checking. I apologize for the late response. 

Below is my formula I placed in Gallery2 Items;

 

Search( 
        If('Dropdown1'.Selected.Result ="All", 'CAOProjectPortfolio', 
         'Dropdown1'.Selected.Result <>"All", 
       Filter('CAOProjectPortfolio', 'OverallStatus(CW)' = 'Dropdown1'.Selected.Result), 
         'Dropdown1'.Selected.Result ="All",'CAOProjectPortfolio',
       Filter( 
            'CAOProjectPortfolio','OverallStatus(CW)' = 'Dropdown1'.Selected.Result)), 
      txtSearchProj.Text,"ProjectName","ProjectManager","Category")

 

I also made a formula for 'OnVisible' which helped

 

ClearCollect(Table11, {Result: "All"});
Collect(Table11, Distinct('CAOProjectPortfolio','OverallStatus(CW)'))

 

And with all of these, I was able to filter the gallery colors using my dropdown.

View solution in original post

Hi @Au_secours ,

You do not need all of that

Search( 
   Filter(
      'CAOProjectPortfolio', 
      'Dropdown1'.Selected.Result ="All" || 
      'OverallStatus(CW)' = 'Dropdown1'.Selected.Result
   ),
   txtSearchProj.Text,
   "ProjectName",
   "ProjectManager",
   "Category"
)

 

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_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Users online (1,406)