cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
cah2035
Helper III
Helper III

Filter SharePoint list where an item has a specific value in their multi-choice column

I have a list of products and one of the columns is a multi-choice column for "category", and a product can be put into multiple categories. In PowerApps, I want to populate a dropdown with these products filtered by a specific category (i.e. filter for items where "CategoryA" exists as one of the items categories). How can I accomplish this (preferably without doing a collection)? 

 

This is just an example of the issue and not reflective of my actual data. I am only going to have 20 or so "products" so delegation will not be an issue. 

1 ACCEPTED SOLUTION

Accepted Solutions
v-xiaochen-msft
Community Support
Community Support

Hi @cah2035 ,

 

According to your description, I've made a test for your reference:

1\ This is my list 'LIST68'. 'Product' is a Single line of text column. 'Category' is a Choice column.It allows multiple selections.

v-xiaochen-msft_0-1612321703868.png

 

2\ Add a combo box control 'ComboBox2' and set its Items property to:

Choices(LIST68.Category)

3\ Add a gallery control and set its Items property to:

Filter(ForAll(LIST68,If(CountRows(Filter(ForAll(ComboBox2.SelectedItems,If(ThisRecord.Value in LIST68[@Category].Value,ThisRecord)),!IsBlank(Value)))>0,ThisRecord)),!IsBlank(Category.Value))

 

4\ The result is as follows:

v-xiaochen-msft_1-1612321863131.png

v-xiaochen-msft_2-1612321889332.png

 

 

Best Regards,
Wearsky
If my post helps, then please consider Accept it as the solution to help others. Thanks.

 

View solution in original post

3 REPLIES 3
v-xiaochen-msft
Community Support
Community Support

Hi @cah2035 ,

 

According to your description, I've made a test for your reference:

1\ This is my list 'LIST68'. 'Product' is a Single line of text column. 'Category' is a Choice column.It allows multiple selections.

v-xiaochen-msft_0-1612321703868.png

 

2\ Add a combo box control 'ComboBox2' and set its Items property to:

Choices(LIST68.Category)

3\ Add a gallery control and set its Items property to:

Filter(ForAll(LIST68,If(CountRows(Filter(ForAll(ComboBox2.SelectedItems,If(ThisRecord.Value in LIST68[@Category].Value,ThisRecord)),!IsBlank(Value)))>0,ThisRecord)),!IsBlank(Category.Value))

 

4\ The result is as follows:

v-xiaochen-msft_1-1612321863131.png

v-xiaochen-msft_2-1612321889332.png

 

 

Best Regards,
Wearsky
If my post helps, then please consider Accept it as the solution to help others. Thanks.

 

Hi @v-xiaochen-msft 

 

Thanks for the help. The situation you set up is similar to what I am trying to accomplish, but not exact. My input in powerapps will not be a combobox, but will be a single value being passed along ("CategoryB" for example), and my output will be a dropdown (not combobox) with Products that contain Category B. The example belong uses the same list you had put together, but my dropdown is not filtered yet. Since I am only passing one value into the formula, there is probably a ForAll in your example that can be removed since I don't need to loop through multiple values. Can your formula be simplified to account for this?

cah2035_0-1612372159133.png

 



@v-xiaochen-msft I played around with the formula and believe I got what I need. 

 

Filter(
  ForAll(
    LIST68,
    If(
      varSelectedCategory in ThisRecord.Category.Value,   
      ThisRecord.Product
    )
  ),
  !IsBlank(Value)
)

//Where varSelectedCategory is the single value that I am passing to the formula i.e. CategoryA

 

So this seems to work for me when I only need to check for a single Category value and not more than one. 

Helpful resources

Announcements
Power Apps Africa Challenge 2022

Power Apps Africa Challenge

Your chance to join an engaging competition of Power Platform enthusiasts.

Super User 2 - 2022 Congratulations

Welcome Super Users

The Super User program for 2022 - Season 2 has kicked off!

September Events 2022

Check out all of these events

Attend in person or online, there are incredible conferences and events happening all throughout the month of September.

Government Carousel

New forum: GCC, GCCH, DoD - Federal App Makers (FAM)

In response to the unique and evolving requirements of the United States public sector, Microsoft has created Power Apps US Government.

Users online (2,389)