Many of you may already have faced some delegation limitations when using SharePoint as a data source for your canvas apps.
When the operation or filter is delegable, it means the query results are processed by the data source and returned to Power App rather than being processed locally in the App, with a maximum of 2000 items to be returned per call. For performance reasons, the default data row limit for non-delegable queries is only 500 items per call but can be increased to 2000 items. If a data source has more than the configured limit and a query with non-delegable operators or filters is executed, inaccurate results will likely be retrieved.
An operator that is not delegable and is missed is the ‘in’ operator (as a test of membership).
I have seen some approaches around the web to simulate the behaviour of an ‘in’ operator in Canvas apps with SharePoint as a data source, but some of them are not based on dynamic selection or are based on hiding or showing content and not based on techniques to filter data differently.
An example of content to be filtered where issues may happen
In the example below, a demo list was created with almost 60000 items.
There are more than 2000 items related to each Ireland county (it’s a lookup field to another list) so that it’s possible to visualise how the filters with delegation issues don’t work properly and how the approach shown later in the article can help to overcome this limitation.
In the following examples, this list will be filtered by the County field, based on a multiple selection combo box. The idea is to filter list items that have any of the chosen filters as County.
Filter example with the delegation issues: “in” Operator
When default ‘in’ operator based in a combo box selection is used in a formula to filter SharePoint list items, the ‘in’ operator is highlighted with the delegation warning:
In all Power App examples used in this post, the count of items will be shown in a label on top of the gallery for better understanding. When selecting a county among the first results returned by the data source, the proper results are retrieved (limited to 2000 items). But if any other county is selected, zero items are returned.
This is due to the issue explained above, Power Apps gets the first results from the data source (first 2000 items from the SharePoint list), and then applies the filter locally in the records that were retrieved. As ‘Cavan’ was not among the first 2000 results, nothing is retrieved.
How to implement a membership filter what will work as the ‘in’ operator
The idea of this approach is to iterate through the chosen filters using a ForAll function. Then query the data source using the delegable equals ‘=’ filter for each of the filter values and return the query results (it works like a ‘map’ operator).
By only using the ForAll and the combination of the filter, it works but the results are obtained in groups (per county filtered). This could be easily used with nested galleries, as in the picture below (the inner gallery uses the child records collection as items):
To make the results become a flat collection, we can use the Ungroup function. By doing this, the result of the ForAll loop can be ungrouped and all items will then lie at the same level.
The limitation is that a maximum of 2000 items can be retrieved per data source call. The batch load that normally works for galleries when they are scrolled will not work when using the formula below. This is due to the results being retrieved and generated once, as even the Filter function is delegable, ForAll is not.
By using the above formula as the items property for a gallery, when filtering the list by multiple filters thousands of values can be retrieved.
This approach also has the limitation of the maximum of 2000 items per specified value in the collection of values to be compared as a filter. If the real number of items in the list is more than 2000 obtained by PowerApps, only the first 2000 among the matching records will be retrieved.
For example, the following SharePoint list view for the demo list is filtered by County ‘Cavan’. The item count has more than the results retrieved by the app (2294).
If the list is filtered by County ‘Cavan’ and status ‘Active’, 803 items are found:
If filtered by two counties (‘Cavan’ and ‘Carlow’) and Status ‘Active’, 1806 items would be found:
By applying additional filters in the Power App formula, the query will be more precise, and the right results are obtained, for example, you could modify the formula to formula mentioned earlier to filter by Status ‘Active’:
If this formula is used as the items property in a filtered gallery, the correct number of items is loaded:
Conclusion:
The approach demonstrated in this post works better than using the ‘in’ operator but has some drawbacks, so use it carefully.
For each item used as a filter in the loop, a data source call will be executed. Which means that for a big collection of filters it may not work fine due to the number of items retrieved, and the number of calls done.
Imagine a case with 20 values selected as filter and 2000 items retrieved per item…40000 items would be returned in the full operation, which would be a heavy operation for both the app and the data source. For better performance, it is recommended to use some delegable filters in combination with this technique in the Filter function call or reduce the number of items in the collection used in the ForAll function.
Even though SharePoint online has automatic indexes activated by default on new lists, which allows your queries to overcome the SharePoint 5000 items threshold by filtering on indexed fields, there is a hard limit of 20 indices per list. Plan your indices accordingly so you make sure the most important ones will be set up.
[…] Alternative to the ‘in’ operator for Power Apps when filtering SharePoint data […]
Is this solution applicable for membership filter ‘in’ operator in sql server?
Hi Frank, I haven’t tested it using SQL, but the formulas would be similar for other underlying data sources as SQL/CDS.
Hi Michel,
Thank you for this great tutorial! How would you go about filtering a SharePoint multiple selection column instead of it being a multiple selection combo box in PowerApps? So for example, let’s say I have a multi selection choice column in SharePoint where you can choose Dog, Cat, and/or Turtle. Then in PowerApps, I have a single selection dropdown that have these same values but I use this dropdown to filter the list (in a gallery) to see all the rows that contain Dog, Cat, or Turtle in the multi selection choice column. How would I do this without using the “in” operator? Hopefully this makes sense.
Hi Stephen,
Thanks for the feedback!
Unfortunately there is no delegable way to filter a multi value column operation as at the moment for SharePoint multi-value column filters are not delegable…so we would have to stick with the ‘in’ operator which is not delegable or use a trick with Filter/LookUp function (which would also not be delegable for this type of field)…
Some options:
Filter(YourList, CurrentFilterValue in MultiValueField.Value )
Filter(YourList, !IsBlank(LookUp(MultiValueField,Value = CurrentFilterValue)))
Filter(YourList, !IsEmpty(Filter(MultiValueField,Value = CurrentFilterValue)))
The filters mentioned can be used in combination with this tutorial techinique, but the only thing is the delegation issue, which can be a problem to you.
Hi Michel,
Ah I see… That’s unfortunate… What would be the best alternative to multi-value columns in SharePoint that would be delegable but provides similar functionality?
Hi Stephen,
I still haven’t found an alternative 😂
Hi Stephen,
I have been searching for a solution for this for a while now.
The following video should provide what you need.
https://www.youtube.com/watch?v=44j2VRbdWjk
Hi James,
Thanks for the suggestion! I have actually come across that video before, however, when I try to implement it, I find that the gallery sometimes does not load/display items that meet the filtering criteria. I believe this has to do with the fact that the gallery loads 100 items at a time, so if the first item that meets the filtering criteria is in the 101st position, the gallery will be completely blank. That was my experience with it. Curious to hear if you have had a similar experience with missing items or have a solution.
Hi,
Can you please recommend how to remove duplicate values on the result of ForAll or Ungroup.
In my case some of the values returned by each Filter operation are same. After collecting all results I want to remove duplicates. Can you please recommend how to do it.
Ex: First filter expressions returns “A”, “B”, “C”. Second call to filter returns “A”, “D”. If I use current formula result after ungroup is “A”, “B”, “C”, “A”, “D”. Note “A”, is repeated. I want the final result to be “A”, “B”, “C”, “D”
Can you please help.
Hi,
You can use the ‘Group’ function to obtain distinct results, for example group the results by the values you need, and then remove the grouped column from the output using the ShowColumns function to access only the columns you need from your source/collection.
Hi Michel,
Many thanks for this tutorial, I’ve found it very useful. However, as I am fairly new to Powerapps I am a little stuck on the syntax required to multiple comboboxes, i.e. I have the comboboxes cmbUser and cmbRegion which I want to use to apply filters in the gallery. I assume I would need to call RenameColumns twice for this, but I’m at a loss as to how to do this? Any help would be much appreciated, thanks.
Hey Matt, no problem. Hope it is helpful.
One idea you can use is to do the filters separately in two statements and then get the distinct results…
This other post might help (to merge two collections of items and get distinct results): https://michelcarlo.com/2021/04/28/power-apps-merge-tables-collections-and-get-distinct-records-without-using-collect/
I tried this and it works great with a SQL dataset.
Hi Tony, great to hear this😁
Hello, this formula has worked for me in many cases, thanks for that, when I use it when filtering a collection, I don’t know why it shows me the records that meet the filter with information, but it also shows me the records that do not meet the filter. the filter but without information, for example I have a collection of 20, and of these 20, 10 meet the filter, this formula brings me the 10 that meet the filter showing information about it, and in the same way it brings me the other 10 blank No information, but it shows it in the gallery. Is there a way to fix that?
Hello, can you share the exact formula you used and more details about your values?