Help filtering from selected items of a ComboBox

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

Hello everyone,

I have this problem I can't solve

There elements I working on look like this

Area Brand

A001 Brand1

Brand2

A002 Brand3

Brand4

Brand5

A003 Brand6

Ok, so I have a ComboBox you can select the Area but is possible to select more than one and then I need to show all the brands that are in these areas

In that case how I can filter to make it show the brands of the areas selected?

I read somewhere I should use the operator In but is not working, there a problem that says it may not work when working with a big group of data (in spanish: Advertencia de delegacion. Es posible que la parte resaltada de esta formula no funcione correctamente en conjunto de datos de gran tamaño)

The synthax Im using is this

Filter(Data source, column name in combobox.selecteditems.column name)

Any idea?

Thank you in advance

Categories:

Building Power Apps

I have the same question (0)

All responses (9)

Answers (0)

Sort by

mdevaney 29,991 Moderator on at

Like
a
(1)

Report
Copy link

Link copied!
@Anonymous

Yes, this code will trigger a delegation error because IN cannot be delegated.

Filter(Data source, column name in combobox.selecteditems.column name)

My question is: will you ever have more than 2,000 rows in your datasource? If no, then you can safely ignore the warning. By the way, you can increase the delegation limit to 2,000 rows in the advanced settings.

Here's a diagramof what can be delegated in SharePoint

https://powerapps.microsoft.com/en-us/blog/sharepoint-delegation-improvements/

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hi,

Unfortunately my data base has much more than 2000 rows of data
In that case, how can I filter it?
Looking around I read about using collections but I'm not really sure as I'm not used to powerapps yet

Was this reply helpful? Yes No
mdevaney 29,991 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!
@Anonymous

If we use a single ComboBox with multiple selections the delegation warning will be unavoidable. Only the equals ( = ) operator can be delegated with Text columns. That's OK. There is a workaround. Instead we can use multiple ComboBoxes each with a single selection.

For example, let's say you want the user to be able to select up to 3 areas. Create 3 Comboboxes in total. Then use can use this code to FILTER your datasource

Filter( your_datasource_Name, Area = ComboBox1.Selected.Area Or Area = ComboBox2.Selected.Area Or Area = ComboBox3.Selected.Area )

Collections won't really work here because they are limited to 2,000 rows.

If you are interested in learning more about collections I posted an app to the community gallery. There's 50+ examples of things you can do with them and it gives you both the code and a visual so you can see what's going on

Link To Collections Cookbook App:

https://powerusers.microsoft.com/t5/Community-App-Samples/Collections-Cookbook-50-Visual-Examples-amp-Code/td-p/437594

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

Was this reply helpful? Yes No
v-xida-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @Anonymous ,

Could you please share a bit more about the data source you used in your app? Is it a SP List?

Could you please show more details about the 'Area' column and 'Brand' column in your data source? Are they Choice type column?

I assume that the 'Area' column and 'Brand' column are both Choice type column in your SP List data source, please consider take a try with the following workaround:

Set the Items property of the Brand ComboBox to following:

Filter( Choices('SP List'.Brand), Value in ForAll( Filter('SP List', Area.Value = AreaComboBox.Seleced.Value).Brand, Brand.Value ) )

Currently, the 'in' operator could not be delegated within SP List, if the amount of your SP List records is more than 2000, I afraid that the above formula would not work in your app.

As an alternative solution, you could consider bulk-load the SP List records into collection in your app, then use the collection as data source in your app instead of the original SP List data source. Please check and see if the following threads would help in your scenario:

https://powerusers.microsoft.com/t5/General-Discussion/Pulling-in-large-ish-SQL-tables/m-p/243777#M71518

https://powerusers.microsoft.com/t5/Building-Power-Apps/Sort-gallery-with-multiple-fields/td-p/379730

https://powerusers.microsoft.com/t5/Building-Power-Apps/Delegation-Formula-Help/m-p/394039#M115089

Best regards,

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @v-xida-msft!
Thank you for your response
About the type of data I'm not sure what are you reffering but what I mentioned was an example
when I say area I mean something like "Men clothes" and brand can be Nike, Zara, Adidas, etc...

For now I'm trying to change that part of my app to something I can manage and do something similar
Thank you everyone anyways

Was this reply helpful? Yes No
mdevaney 29,991 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

@Anonymous

Did you try the multiple Comboboxes approach I mentioned? It will definitely work. The question is: are you OK with how it would change your UI? Please note that you can make the 2nd and 3rd ComboBoxes not visible until the previous box is filled in.

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Ok, so I have a ComboBox you can select the Area but is possible to select more than one and then I need to show all the brands that are in these areas
In that case how I can filter to make it show the brands of the areas selected?
I have been saw that features on the xxxtentaacion merch store then I have been decided to discuss and implemented on my site as well.

Was this reply helpful? Yes No
Fernanda 380 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi
I had a similiar problem that I was able to figure out with this video:
https://www.youtube.com/watch?v=44j2VRbdWjk&t=123s

It works with the Visible property. Maybe it would work for you aswell

Was this reply helpful? Yes No
jber8 2 on at

Like
a
(0)

Report
Copy link

Link copied!

Using your example table above, I am filtering my combo box in the following way:
Distinct(Filter(Data Source, Area in AreaComboBox.SelectedItems), Brand)

The distinct will make sure you don't get any duplicate brands that appear in more than one area, but you still might get the delegation warning.

Hope this works for you!

Was this reply helpful? Yes No