cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
karolyuan
Helper I
Helper I

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

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

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
v-chenzhi
Community Support
Community Support

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.

vchenzhi_1-1640167307651.png

 

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

 

vchenzhi_2-1640167339425.png

vchenzhi_3-1640167350613.png

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

View solution in original post

2 REPLIES 2
v-chenzhi
Community Support
Community Support

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.

vchenzhi_1-1640167307651.png

 

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

 

vchenzhi_2-1640167339425.png

vchenzhi_3-1640167350613.png

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
Helper I
Helper I

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

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power Platform Call June 2022 768x460.png

Power Platform Community Call

Join us for the next call on June 15, 2022 at 8am PDT.

PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

PA.JPG

New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

Top Solution Authors
Top Kudoed Authors
Users online (3,168)