Filter gallery using a combo box that allows multiple selections

[SachS]

I have a SharePoint list with two columns: Title and MyChoices.

Title = Text column. 

MyChoices = 'Choice' type. Allows multiple selections. Allows 'fill in' values. Pre-set values for MyChoices are A, B, C, and D.

The actual list sample values look like this:

I have a PowerApp created using this list, with a Gallery and a ComboBox. The idea is to filter the gallery using the combo box selections. I specifically have two requirements:

1.  User be able to all distinct values of MyChoices (remember, it allows fill-in values) in the combo box.
   • This means, according to above sample data, the combo box should display A, B, C, D, Z as selection items.
2. Gallery is filtered by multiple choices.
   • That is, for instance, if the user selected A and Z in the combo box, the Gallery should filter to show items 1, 5, 6, and 8.

My code is like this so far:

ComboBox.Items

Distinct(
    Ungroup(
        ForAll(
            _DeleteThis_MultipleChoice,
            {Item: MyChoices}
        ),
        "Item"
    ),
    Value
)

Gallery.Items

Filter(
    _DeleteThis_MultipleChoice,
    IsBlank(ComboBox1.SelectedItems.Result) || IsEmpty(ComboBox1.SelectedItems) || ComboBox1.Selected.Result in MyChoices.Value
)

This works partially. The first two checks takes care of empty items, and the last one filters the list by the user choice. But the issue here is obviously, it only takes into account the last choice the user made. So if the user selected in the combo box A and Z in that order, the list would filter only to show item #8.

I understand that this is because I'm using ComboBox1.Selected.Result which is a single select. I've tried to do ComboBox1.SelectedItems in MyChoices.Value, but it gives me an error:

How do I fix this?

[BCBuizer]

Hi @SachS ,

Using the Choices() function it is a lot easier to get the distinct values for the MyChoices column:

ComboBox.Items = Choices(_DeleteThis_MultipleChoice.MyChoices)

Still you will have to add .Value after SelectedItems to take care of the error:

Filter(
    _DeleteThis_MultipleChoice,
    IsBlank(ComboBox1.SelectedItems.Result) || IsEmpty(ComboBox1.SelectedItems) || ComboBox1.SelectedItems.Value in MyChoices.Value
)

[SachS]

@BCBuizer 

Thanks for  the reply.

The use of Choice() in Combo Box won't work for me, since, as I mentioned in the post that the [ MyChoices ] is a Choice column but also allows fill in values by the user, and I need the ComboBox to present those fill in values as options to filter as well.

Re. the Gallery.Item, it still doesn't work even if I add .Value. Please see below:

[BCBuizer]

Hi @SachS ,

In that case, replace .Value for .Result

Filter(
    _DeleteThis_MultipleChoice,
    IsBlank(ComboBox1.SelectedItems.Result) || IsEmpty(ComboBox1.SelectedItems) || ComboBox1.SelectedItems.Result in MyChoices.Value
)

[SachS]

@BCBuizer it's still the same.

If I use Choices() function in the ComboBox, my code looks like this in Gallery.Items:

Filter(
    _DeleteThis_MultipleChoice,
    IsBlank(ComboBox1.SelectedItems.Value) || IsEmpty(ComboBox1.SelectedItems) || ComboBox1.SelectedItems.Value in MyChoices.Value
)

And if I use the more elaborated function I showed earlier in the ComboBox, then I must use .Result, like so:

Filter(
    _DeleteThis_MultipleChoice,
    IsBlank(ComboBox1.SelectedItems.Result) || IsEmpty(ComboBox1.SelectedItems) || ComboBox1.SelectedItems.Result in MyChoices.Value
)

But in either case, while the ComboBox.SelectedItems.Value or ComboBox.SelectedItems.Result works for the IsBlank() check, it gives the above mentioned "Invalid argument type. Cannot use Table values in this context" error message for the code after the last OR in the code shown. See below:

[WillPage · Accepted solution]

Try this:

Filter(_DeleteThis_MultipleChoice, CountRows(Filter(MyChoices,Value in ComboBox1.SelectedItems)) > 0)

[BCBuizer]

Hi @SachS ,

So PowerApps turns out not to like an N:N search in this manner. Please check this video on how you can achieve the desired result: https://www.youtube.com/watch?v=YYizaX6gXW0

[SachS]

This was what I was looking for, thanks!