Filter based on multi select column cannot return proper records when selection order is reversed

[karolyuan]

I have a SharePoint List recording some contacts or investigators and I would like to filter them out based on a multi-select column called "Specialties". I have used the code as below to filter but it can only filter based on the order in the list.  Could you please figure out any other way to filter so that the order in the combobox selection can no longer be a concern?

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-chenzhi · Accepted solution]

Hi @karolyuan ,

I assume that the gallery only shows the records equal to choice1 and choice2 when the ComBobox selects choice1 and choice2. I have test on my side, please try the following workaround.

SortByColumns(
    Filter(
        'Vendors and Contacts list',
        Sum(
            ForAll(
                ComboBox1.SelectedItems,
                If(
                    !(Value in Specialties.Value),
                    {TheValue: 1},
                    {TheValue: 0}
                )
            ),
            TheValue
        ) < 1
    ),
    "Title"
)

Please try to modify your formula like this.

SortByColumns(
    Filter( 
            'Vendors and Contacts list',      
        Country.Value = drp_CountriesChoice.Selected.Value,
        StartsWith(
            'Contact Name',
            txtin_ContactCompanyFill.Text
        ) || StartsWith(
            'Company Name',
            txtin_ContactCompanyFill.Text
        ),
        Sum(
            ForAll(
                ComboBox1.SelectedItems,
                If(
                    !(Value in Specialties.Value),
                    {TheValue: 1},
                    {TheValue: 0}
                )
            ),
            TheValue
        ) < 1
    ),
    "Title"
)

Best Regards,

Zhi Chen

[karolyuan]

Thank you so much! This is really a life saver. I can finally apply filter as I want