Filtering browsegallery by two dropdowns

(0) Share

Report

Posted on by wonka1234

909 Super User 2024 Season 1

Hi,

I am trying to have two filters for my browsegallery but cant seem to get it to work, some records get dropped when im filtering . I want the filters to be combined, ie Filter 1 AND Filter 2 or vice versa will drill down records nicely.

The filters work but when I reset it to either "Filter for Request Status" or "Filter for GCO Lead" records for missing.

Am I doing this correctly?

SortByColumns(
	Filter(
		[@'Data Source'],
		If(
			Dropdown3.Selected.Value = "Filter for Request Status", 
			Status_two.Value = "New Request" Or Status_two.Value = "GCO Assessment" Or Status_two.Value = "Pending SM Review of Date" Or Status_two.Value = "Complete", 
			Status_two.Value = Dropdown3.Selected.Value
		)
 And
 If(
 Filter_for_GCO_Lead.Selected.Value = "Filter for GCO Lead",
 'GCO Lead_three' = "email1@gmail.com" Or 'GCO Lead_three' = "email2@gmail.com",
 'GCO Lead_three' = Filter_for_GCO_Lead.Selected.Value
 )
		

	),
	"Title",
	If(
		SortDescending1,
		SortOrder.Descending,
		SortOrder.Ascending
	)
)

All responses (5)

Answers (2)

Verified answer

LaurensM 12,510 Super User 2025 Season 1 on at

Like (1)

Report
Re: Filtering browsegallery by two dropdowns

Hi @wonka1234,

I would use nested filtering since the StartsWith functions will make our filter query non-delegable (Delegable StartsWith expects a Text field). Our original code will remain delegable and afterwards we will filter that pre-filtered dataset with our StartsWith functions:

SortByColumns( Filter( Filter( [@'Data Source'], (Dropdown3.Selected.Value = "Filter for Request Status" && (Status_two.Value = "New Request" || Status_two.Value = "GCO Assessment" || Status_two.Value = "Pending SM Review of Date" || Status_two.Value = "Complete")) || (Dropdown3.Selected.Value <> "Filter for Request Status" && Status_two.Value = Dropdown3.Selected.Value), (Filter_for_GCO_Lead.Selected.Value = "Filter for GCO Lead" && ('GCO Lead_three' = "email1@gmail.com" || 'GCO Lead_three' = "email2@gmail.com")) || (Filter_for_GCO_Lead.Selected.Value <> "Filter for GCO Lead" && 'GCO Lead_three' = Filter_for_GCO_Lead.Selected.Value) ), StartsWith(Control_two.Value, 'search box'.Text) || StartsWith(ID_three, 'search box'.Text) ), "Title", If( SortDescending1, SortOrder.Descending, SortOrder.Ascending ) )

I hope this helps!
wonka1234 909 Super User 2024 Season 1 on at

Like (0)

Report
Re: Filtering browsegallery by two dropdowns

@LaurensM

Hi Laurens, wanted to ask how to append another search box to the above statement.
I want to add -

(StartsWith( Control_two.Value, 'search box'.Text ) Or StartsWith( ID_three, 'search box'.Text ))

How can I add this to the above statement? Ive tried it as is and the filters dont really seem to work that well. It is a text input search box i am trying to filter by and also the dropdowns from above.
LaurensM 12,510 Super User 2025 Season 1 on at

Like (1)

Report

Re: Filtering browsegallery by two dropdowns

@wonka1234,

I would expect it to be something related to spelling or a missing option. Do those records have something in common regarding status or GCO Lead? Or are you missing a certain status or GCO Lead in your gallery?
wonka1234 909 Super User 2024 Season 1 on at

Like (0)

Report

Re: Filtering browsegallery by two dropdowns

@LaurensM thanks... seems to be working okay, however I am missing two records ...i am trying to verify spelling but everything seems to be okay. Any reason as to why some would be filtered out without me knowing? if I remove these filters I can see them.
Verified answer

LaurensM 12,510 Super User 2025 Season 1 on at

Like (1)

Report
Re: Filtering browsegallery by two dropdowns

Hi @wonka1234,

You can avoid the If() statements within your Filter (causing a non-delegable query) by making the following adjustment:

SortByColumns( Filter( [@'Data Source'], (Dropdown3.Selected.Value = "Filter for Request Status" && (Status_two.Value = "New Request" || Status_two.Value = "GCO Assessment" || Status_two.Value = "Pending SM Review of Date" || Status_two.Value = "Complete")) || (Dropdown3.Selected.Value <> "Filter for Request Status" && Status_two.Value = Dropdown3.Selected.Value), (Filter_for_GCO_Lead.Selected.Value = "Filter for GCO Lead" && ('GCO Lead_three' = "email1@gmail.com" || 'GCO Lead_three' = "email2@gmail.com")) || (Filter_for_GCO_Lead.Selected.Value <> "Filter for GCO Lead" && 'GCO Lead_three' = Filter_for_GCO_Lead.Selected.Value) ), "Title", If( SortDescending1, SortOrder.Descending, SortOrder.Ascending ) )

Additionally, make sure that all available options are in the condition (you have 2 emails for GCO and 4 statusses). When e.g. Filter for Request Status or Filter for GCO Lead is selected, only a few option will be displayed. Make sure those options are written correctly - pay close attention to spaces and capitalization.

If this solves your question, would you be so kind as to accept it as a solution & give it a thumbs up.

Thanks!