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 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.

Carousel_PP_768x460_Wave2 (1).png

2022 Release Wave 2 Plan

Power Platform release plan for the 2022 release wave 2 describes all new features releasing from October 2022 through March 2023.

365 EduCon 768x460.png

Microsoft 365 EduCon

Join us for two optional days of workshops and a 3-day conference, you can choose from over 130 sessions in multiple tracks and 25 workshops.

Top Solution Authors
Users online (2,920)