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
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 (2,259)