Using dropdown to select which column to filter gallery by

[Trachypithecus]

Very new to Power Apps and am still finding my way around how things work.

I'd like to create an app that primarily consists of a gallery that filters based on the value of a dropdown or (ideally) a combo box.  I've included a very simplified example of what my data looks like in Excel, but in actuality it's hundreds of items and dozens of species.

Item	Category	Cats	Dogs	Birds
Blanket	Bedding	Yes	Yes	Conditional
Cardboard Tube	Shred	Yes	Conditional	Yes
Frisbee	Play	No	Yes	No
Hose	Play	No	Yes	No
Kong	Feeder	Yes	Yes	Conditional
Mirror	Sensory	Yes	No	Yes
Rope Toy	Play	Yes	Yes	No
Shavings	Shred	Conditional	No	Yes
Tennis Ball	Play	Yes	Yes	No

I'd like to have a dropdown of all the available species (columns) and for the gallery to only display the items where approval status is 'Yes' for that species.  So if I were to select Birds, it would only display Blanket, Cardboard Tube, Kong, Mirror, and Shavings.  Ideally, it would be a combo box where I could select both Cats and Birds and it would display only items that are allowed for both, but I can't even get this to work with a dropdown right now.

Items for Dropdown1:

["Cats","Dogs","Birds"]

Items for the gallery:

Filter(Table1,Dropdown1.Selected.Value = "Yes")

I've tried tweaking it in a number of different ways and can't find something that works in which the gallery populates with the filtered data that I want in this way.  

It works if I refer to each column specifically in the filter formula, like this

Filter(Table1,Birds="Yes")

But I can't figure out whether or not it's possible to dynamically change which column(s) in the data the formula is checking against.

I think I could probably come up with some complicated thing that more or less achieves what I want with a giant pile of If or Search functions, but I feel like there's either a really simple solution staring me in the face or I've got a huge misunderstanding of how the filter function can work in Power Apps.

Am I missing something?  Thanks.

[eka24]

I suggest you make use of search function  on the Gallery items as:

    If(IsBlank(SearchMe),YourDatasourceName,
        Search(YourDatasourceName,SearchMe.Text,"Dogs","Cats","Birds"))

Or

Create a Collection OnVisible of the Screen and use the collection name instead of your datasouce in the search as:
  ClearCollect(MyCollData,YourDatasourceName)

Then in the Gallery items:
    If(IsBlank(SearchMe),MyCollData,
        Search(MyCollData,SearchMe.Text,"Dogs","Cats","Birds"))

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

[v-yutliu-msft]

Hi @Trachypithecus ,

Do you want to filter Item field based on ["Cats","Dogs","Birds"]​  fields?

Firstly, let me explain whey you failed by using the formula that you mentioned.

The fieldname should be constant value in Filter function.

Dropdown1.Selected.Value is a dynamic value, which could not be used as field name.

I suggest you use If statement to justify which field name is selected.

Try this:

1)set drop down's Items:

["Cats","Dogs","Birds"]

2)set gallery's Items:

If(Dropdown1.Selected.Value="Cats",
Filter(Table1,Cats="Yes"),
Dropdown1.Selected.Value="Dogs",
Filter(Table1,Dogs="Yes"),
Dropdown1.Selected.Value="Birds",
Filter(Table1,Birds="Yes")
)

 Then the gallery will filter based on different columns, the column is based on the value you select in drop down.

If the problem still exists, please tell me your fields data type. I all assume them as text type in my test.

Best regards,

[Trachypithecus]

That method did work for me, thanks.

Playing around with it a bit more, I was able to find another option by rearranging the data into Yes, Conditional, and Prohibited columns.

Item	Approved	Conditional	Prohibited
Blanket	Cats, Dogs	Birds
Cardboard Tube	Birds, Cats	Dogs
Frisbee	Dogs		Birds, Cats
Hose	Dogs		Birds, Cats
Kong	Cats, Dogs	Birds
Mirror	Birds, Cats		Dogs
Rope Toy	Cats, Dogs		Birds
Shavings	Birds	Cats	Dogs
Tennis Ball	Cats, Dogs		Birds

And using Search to display approved items in the gallery based on which species was selected in the dropdown:

Search(Table1,Dropdown1.Selected.Value,"Approved")

Is there any advantage to filtering with If statements rather than this Search method, or would it not really matter performance-wise?

And if I were to store the data in a Sharepoint list (instead of in Excel) with each column as a Choice column, would that cause any problems using this Search method?

Thanks.

[eka24]

Encourage you to use collections whether Search or Filter. Collections inside your app rather than the Table1 would largely improve performance.

There and are many downsides to Excel compared to Sharepoint. If you want to move to sharepoint that would be fine.

And sharepoint choice columns could be used in powerapps:

For more on choices
https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-choices

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

[v-yutliu-msft · Accepted solution]

Hi @Trachypithecus ,

1)Using search function will be better then if statement.

If statement needs many justifications, which will  reduce performance-wise.

2)I'm afraid it's not supported to use Search function for Choice date type.

Search function only works for Text type.

You have two soluions:

• use text type , not choice type
• use Filter function in stead of search function,

for example:

the drop down's Items:

Choices(listaname.Approved)

the gallery's Items:

Filter(Table1,Dropdown1.Selected.Value="Approved")

Best regards,

[rohchsita21]

Hi, any solution to this question? 
Connecting "All" option from the dropdown list to ... - Power Platform Community (microsoft.com)