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

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.

 

View solution in original post

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
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

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