Filter multiple selection choice column

[kenneth0596]

Hi, I have a data table pulling data from SharePoint list, it consists of multiple columns and one of the columns (Industry Experience) is a choice field type with multiple selections. I would like to filter the data table by a combo box (Items: Choices('SharePoint List'.'Industry Experience')). 

This is what I have done so far: replaced the text of Industry Experience column in the data table with this formula: Concat('Industry Experience',Value,", "). And then I have tried all following formulas for data table items and none of them worked.

            1. Filter('Offer/Request List',Concat('Industry Experience',Value,", ")=ComboBox1.Selected.Value in 'Industry Experience')

            2. Filter('Offer/Request List',Concat('Industry Experience',Value,", ")=ComboBox1.Selected.Value in Concat('Industry Experience',Value,", ")

            3. Filter('Offer/Request List',ComboBox1.Selected.Value in Concat('Industry Experience',Value,", "))

Can someone please help? Really appreciated! 

[faustocapellanj]

Hi @kenneth0596 

I recently did something similar to this, but instead filtering the data directly from the selected choices in the combobox, I created a collection as I filtered my second table. For example, I have a gallery with multiple checkboxes from a table, and as I select each one, I am building a collection of the second table with code similar to this:

Collect(
    colSampleCollection,
    Filter(Table_2, ThisItem.<property> in Table_2_Column)
)

Then I use the collection in the second gallery. This might not be exactly what you are looking for, but it will be good reference. Please try it and let us know.

Regards,

[v-yutliu-msft · Accepted solution]

Hi @kenneth0596 ,

Do you want to filter based on a multiple choice column by using a multiple choice combo box?

Please notice that we usually use "in" these ways:

1)value in table

2)short value in short value

You could not use table in table or table in value.

In your issue, since the value of a multiple choice field is a table, the combo box's selected items is a table too, you need to transfer them to text both to compare.

Try this:

Filter('Offer/Request List',
Concat(ComboBox1.SelectedItems,Value&",") in
Concat('Industry Experience',Value&",")
)

This formula will filter items that includes all the value selected in the combo box.

(the combo box select a,b; you will only get records with value like this: a,b,c ; ab,d)

If you want to filter items that only includes parts of value selected in the combo box, you could try this:

(the combo box select a,b; you will  get records with value like this: a,c ;b,c ;a,b,c)

ForAll(ComboBox1.SelectedItems,
       Filter('Offer/Request List',
       Value in 'Industry Experience'.Value
             )
       )

Best regards,

[karolyuan]

Hi, I am using your idea on my filter. But it turns out this formula can only filter multi selection in certain order. For example, I have a list which contains information about various researchers and their specialties. If someone is good at Spec1 and Spec2, I can only first choose Spec1 and then Spec2. If I reverse the choice, then the form cannot be filltered out. Please tell me if you have any idea.

SortByColumns(
    Filter(
        AddColumns(
            'Vendors and Contacts list',
            "Spec2",
            Specialties
        ),
        Country.Value = drp_CountriesChoice.Selected.Value,
        StartsWith(
            'Contact Name',
            txtin_ContactCompanyFill.Text
        ) || StartsWith(
            'Company Name',
            txtin_ContactCompanyFill.Text
        ),
        Concat(ComboBox1.SelectedItems, Value, ", ") in Concat(Spec2, Value, ", ")
    ),
    "Title"
)

[Hsnowbee]

@v-yutliu-msft- Thank you for this solution! I used it to filter a Dataverse multiselect choice field in a gallery where the user can select multiple options from a combo box. Since I only have 700 records, it's working well - but I do have a delegation warning. Is there a delegable work-around to avoid using the "in" function and accomplish the same thing? I expect to have more than 2,000 records in my production application. Truly appreciate any help!

Filter(Table, Concat(ComboBox.SelectedItems, Value&", ") in Concat('MultiselectChoiceField'.Value, Value & ", "))

[Venomtver]

I have recently faced the same issue, and my colleague helped me to solve it.
I took the beginning from Reza Dorrani here.

Here is my formula:

Filter
(Source,
IsBlank(combo_box.SelectedItems.Value) || IsEmpty(combo_box.SelectedItems) || true in ForAll(Column.Value, ThisRecord in combo_box.SelectedItems.Value)
)

How the result looks like.

[Hsnowbee]

Thank you @Venomtver - This is very helpful!

[poonam1]

This really helped

[ashish300789]

Hi I have a share point list of coach and they have multiple areas in caoching topics in next column. I used a drop drown to take coach name but getting error in using filer for next dropdown which filters coaching topics based on coach name.. 

Can anyone please help me on that

[Venomtver]

Hello Ashish, please, reach me out through email, i'll try to help you
venomtver@mail.ru