cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kenneth0596
Advocate II
Advocate II

Filter multiple selection choice column

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! 

1 ACCEPTED SOLUTION

Accepted Solutions
v-yutliu-msft
Community Support
Community Support

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,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

10 REPLIES 10
faustocapellanj
Memorable Member
Memorable Member

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,

Fausto Capellan, Jr
Did I answer your question? Mark my post as a solution! Did my response help? Please give it a thumbs up!
v-yutliu-msft
Community Support
Community Support

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,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
karolyuan
Helper I
Helper I

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.

karolyuan_3-1639731285462.png

 

karolyuan_2-1639731183476.png

 

 

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"
)

 

@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
Advocate I
Advocate I

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.

Venomtver_0-1647615793632.jpeg

 

Thank you @Venomtver - This is very helpful!

This really helped

ashish300789
Frequent Visitor

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

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

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Power Apps Ideas

Check out the New Ideas Site

We are excited to announce a new way to share your ideas for Power Apps!

Top Solution Authors
Top Kudoed Authors
Users online (4,362)