Showing results for 
Search instead for 
Did you mean: 
Helper II
Helper II

Filter gallery based on 2nd SP list

Hi, I can’t seem to get my head around this. 

I have a SP with around 5000 items which will grow. One of the fields is a multi choice column. Let’s call this list nLISTdb


I need to filter this column, amongst others E and display filtered results in a gallery. 

I can pull all records into a large merged collection but this is having a hit on performance. So I’d like to delegate the filter to SP.


@RandyHayes suggested one route to be a 2nd SP list. 

I have the following field:


FLAG | recordID


Lets call this list nLISTflag


So when an item is created in nLISTdb, the choices I made for FLAGS are added individually into nLISTflag with the item ID from nLISTdb


So if an item is created like this in nLISTdb:


Choices             |           ID

ABC,XYZ,123     |         5001

This is patched ti nLISTflag as:


FLAG      |      recordID

ABC                  5001

XYZ                   5001

123                   5001

So now I’m trying to filter my gallery to display all the records in nLISTdb which have the ABC flag for example.


The closest I get give me a delegation warning because of ‘in’ which is not delegator. But I can’t figure out another way:

Snippet from my gallery items field.


ID in Filter(nLISTflag, FLAG=varFLAGfilter).recordID


Where ID is the ID of the record in nLISTdb and varFLAGFilter is a combobox selection for example “ABC”


Any help appreciated.


Helper II
Helper II

Anyone any ideas?

Been looking at this today. I’m not sure this is the right approach now. Anyone point me in the right direction? Thanks

Found the answer thanks to @WarrenBelz


This works perfectly! My app can now filter multi choice without delegation issues.

Helper II
Helper II

Ok, so this doesn’t work as expected.

The code works fine. However because I’ve used LookUp() the filter only returns the first item. I need all the matching items returned.


When I substitute A second filter() for LookUp() I get an error. Below…


Incompatible types for comparison…: Number/record





Any ideas? Thanks

Still looking at this.


I’ve found that Delegation issues can be avoided by using a LookUp column with Search(Choices())


This actually works, but I can’t find a way to combine it with my other filters.


There must be a way to delegate filtering of a multi choice / lookup column to SP. 

I’m just missing it!!! All help appreciated.


@WarrenBelz  Any ideas?

Hi @gmbeee ,

You are not missing anything - the Choices workaround you mentioned has some limitations (as you have discovered), but the in filter is simply not Delegable. Filter() will not help here as that simply returns all records with a field matching the first item in the multi-select column. I had a look at a Collection looping through the items, but you still need AddColumns and Split, where both are subject to Delegation limits also.

The best you could do here would be to "pre-filter" both lists to numbers under your delegation limit (maybe the newest 2,000 records) before adding your filter. 

Hi @WarrenBelz 


Thank you for the reply.


The gallery is subject to several combobox filters. The user filters it accordingly.


I’ve gone with a toggle called ‘Large dataset filter’. When checked the toggle sets a variable to true.


I have an if statement in the gallery items field. If the toggle is true, I collect all list records into a collection using an ID mirror column to count the records and collect them in batches. 

This give performance a hit. But only when the toggle is checked.


By default it’s fault and the gallery is filtered with delegation warnings but prefiltered by date. So no performance issues.


Hi @gmbeee ,

Yes - @RezaDorrani has a good video on this process and actually refers in part to the pre-filter process I mentioned - I assume you are following this logic. Is this not resolved?

Helpful resources

Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Community Call Conversations

Introducing the Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (4,673)