cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MBC_DK
Frequent Visitor

Filtering Gallery by exluding dropdown options

My problem is that by Clearcollect I enabled some dropdowns to use the Value "All" + Choices from Share point column.

In the ClearCollect I excluded som options with this formula:

ClearCollect(
AllStatusx;
{Value: "All"};
Filter(
Choices('Source'.'Column');
Not(Value = "X");
Not(Value = "Y");
Not(Value = "Z");
Not(Value = "XX")

The dropdown I use (DD_Status_Migration) has now limited options based on the above, and I am able to filter on the needed options

However when choosing "All", the gallery will display the options I excluded in ClearCollect.

 

How do I ensure that the excluded options will have effect in the linked gallery?

Currently I use these filters in the Gallery Items:

Search(
Filter(
'Source';
DD_Status_Migration.Selected.Value = "All" Or 'Status Migration'.Value = DD_Status_Migration.SelectedText.Value;
DD_Product_Group.Selected.Value = "All" Or 'Product Group'.Value = DD_Product_Group.SelectedText.Value;
'Migration Responsible'.Value = DD_Migration_Responsible.SelectedText.Value
);

Customer_Search.Text;
"Title"
)

1 ACCEPTED SOLUTION

Accepted Solutions
v-bofeng-msft
Community Support
Community Support

Hi @MBC_DK :

Maybe you could try:

Search(
   Filter(
   'Source';
   (DD_Status_Migration.Selected.Value = "All" And ('Status Migration'.Value in ["X";"Y";"Z";"XX"])) Or 
   'Status Migration'.Value = DD_Status_Migration.SelectedText.Value;
   DD_Product_Group.Selected.Value = "All" Or 'Product Group'.Value = DD_Product_Group.SelectedText.Value;
   'Migration Responsible'.Value = DD_Migration_Responsible.SelectedText.Value
   );
   Customer_Search.Text;
"Title"
)

Best Regards,

Bof

View solution in original post

7 REPLIES 7
cwebb365
Solution Sage
Solution Sage

Just basically repeat what you did for the dropdown. Tag on something like 

,Not(DD_Status_Migration.Selected.Value = "X"),

,Not(DD_Status_Migration.Selected.Value = "Y")

 

to the end of that FILTER. 

MBC_DK
Frequent Visitor

Thank you for your input.

I am not sure I understand your suggestion. Would these have to be added in the Gallery Items along with the filters below? I tried multiple ways and variations of what you suggest, without any luck and I lost track of the methods I used.

It would be of great help if you can illustrate it be adding the full code suggested below, because I am struggling with the syntax

Search(
Filter(
'Source';
DD_Status_Migration.Selected.Value = "All" Or 'Status Migration'.Value = DD_Status_Migration.SelectedText.Value;
DD_Product_Group.Selected.Value = "All" Or 'Product Group'.Value = DD_Product_Group.SelectedText.Value;
'Migration Responsible'.Value = DD_Migration_Responsible.SelectedText.Value
);

Customer_Search.Text;
"Title"
)

*Edit incorrect selected.value. 

 

Search(
Filter(
'Source';
DD_Status_Migration.Selected.Value = "All" Or 'Status Migration'.Value = DD_Status_Migration.SelectedText.Value;
DD_Product_Group.Selected.Value = "All" Or 'Product Group'.Value = DD_Product_Group.SelectedText.Value;
'Migration Responsible'.Value = DD_Migration_Responsible.SelectedText.Value;

NOT('Status Migration'.Value = "X");

NOT('Status Migration'.Value = "Y");

NOT('Status Migration'.Value = "Z"); 
);

Customer_Search.Text;
"Title"
)

Wait sorry, not the selected value, would be the column. 

 

Search(
Filter(
'Source';
DD_Status_Migration.Selected.Value = "All" Or 'Status Migration'.Value = DD_Status_Migration.SelectedText.Value;
DD_Product_Group.Selected.Value = "All" Or 'Product Group'.Value = DD_Product_Group.SelectedText.Value;
'Migration Responsible'.Value = DD_Migration_Responsible.SelectedText.Value;

NOT('Status Migration'.Value = "X");

NOT('Status Migration'.Value = "Y");

NOT('Status Migration'.Value = "Z"); 
);

Customer_Search.Text;
"Title"
)

MBC_DK
Frequent Visitor

I tried by adding just one exception and immediately I get the formula errors, so I must be doing something wrong in how I type the formula. I added Not('Status Migration'.Value = "X") See screenshot below

 

2021-09-30 21_07_31-Window.png

Ok so what's the error say? I might just need to be Not('Status_Migration' = "X"); or maybe Not don't work there, you can try 'Status_Migration'.Value <> "X"); etc. 

v-bofeng-msft
Community Support
Community Support

Hi @MBC_DK :

Maybe you could try:

Search(
   Filter(
   'Source';
   (DD_Status_Migration.Selected.Value = "All" And ('Status Migration'.Value in ["X";"Y";"Z";"XX"])) Or 
   'Status Migration'.Value = DD_Status_Migration.SelectedText.Value;
   DD_Product_Group.Selected.Value = "All" Or 'Product Group'.Value = DD_Product_Group.SelectedText.Value;
   'Migration Responsible'.Value = DD_Migration_Responsible.SelectedText.Value
   );
   Customer_Search.Text;
"Title"
)

Best Regards,

Bof

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,762)