Filter a gallery with 2 comboboxes with multiple selections enabled

(0) Share

Report

Posted on by rumenovk

Hi all,

I'd be really grateful if you help me with an issue I have.

I'm trying to filter a gallery based on two comboboxes. Both of the comboboxes get their data from a SP list Choices columns with "Allow multiple selections" option enabled.

I tried so many formulas found here but I always get different error messages (Invalid argument type - Tables/Text cannot be used in this context; Expecting a text value instead, etc).

I got to a point where I have a code without errors for just one of the comboboxes but it doesn't actually filter anything in the gallery.

My 2 comboboxes are BoxProcess (the respective SP column is BusinessProcess and BoxRole (SP column is BusinessRole). Here's the one formula I got to without errors but without any results shown.

Filter(
'DataSource',
If(
IsEmpty(BoxProcess.SelectedItems),
true,
IsBlank(BoxProcess.SelectedItems),
true,
CountRows(
Filter(
'DataSource',
CountRows(
Filter(
BoxProcess.SelectedItems.Value,
Value in BusinessProcess.Value
)
) > 0
)
) > 0
)
)

Can you point me out where is the discrepancy in this code?

Thank you in advance!

Categories:

Building Power Apps

All responses (7)

Answers (1)

Sort by

Cat Schneider 488 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

Is the filter meant to fill or limit the items in the Gallery list? To fill, meaning there's no Gallery items until a record matches a selection; and to limit, in the traditional sense where anything that matches a selection is kept, everything else excluded.

The fields that are to match against the selections, are they multi- or single-select? What about the comboboxes themselves?
rumenovk 11 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi, thanks for replying!
The filters are meant to limit the gallery list - starting from all records to filtering only the ones with the appropriate Process & Role.
The columns BusinessProcess and BusinessRole are Choices with multiple selections enabled.
I can select multiple entries in the combobox:
Cat Schneider 488 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!
So then would the following be accurate... show all items:
Where any BusinessProcess selection matches any BoxProcess selection
AND
Where any BusinessRole selection matches any BoxRole selection
Or would it be:
Where all BusinessProcess selections match all BoxProcess selections
AND
Where all BusinessRole selections match all BoxRole selection
Or some other variation?
rumenovk 11 on at

Like
a
(0)

Report
Copy link

Link copied!

Thank you again and I apologize that I cannot explain it in a better way. If that's my list:
Item BusinessProcess BusinessRole
1 General, Reporting Administrator, HR
2 Reporting HR
3 Close, General Administrator, HR, IT

If I select in the BoxProcess Reporting and in BoxRole HR, it would show 1 & 2
If I select in the BoxProcess General and Reporting and in BoxRole I select Administrator and HR, it would show 1.
If I select in the BoxProcess General and in BoxRole I select Administrator and HR, it would show 1 and 3.
Verified answer

Cat Schneider 488 Most Valuable Professional on at

Like
a
(1)

Report
Copy link

Link copied!
@rumenovk, apologies as I clicked the wrong reply button...
Try this:

Filter('DataSource',(CountIf(BoxProcess.SelectedItems As i, i.Value in BusinessProcess.Value) = CountRows(BoxProcess.SelectedItems)) * (CountIf(BoxRole.SelectedItems As i, i.Value in BusinessRole.Value) = CountRows(BoxRole.SelectedItems)) = 1)
rumenovk 11 on at

Like
a
(0)

Report
Copy link

Link copied!

Thank you so much!! Works like a charm!
Many, many thanks.
rumenovk 11 on at

Like
a
(0)

Report
Copy link

Link copied!

Thank you so much!! Works like a charm!
Many, many thanks.