Answered

Delegation on functions with switch

(0) Share

Report

Posted on by JL-14111105-0

Hi I am creating an app that is used across multiple countries. The app uses Sharepoint as data source but the set of lists changes with the user region. Up to this moment I used something a bite brute-forcish like this (just as an example):

Count(Filter(Switch(varRegion, "Region1", List1, "Region2", List2, "Region3", List3, "Region4", List4), field1 = var1 && field2 = var2).ID)

What I noticed is that the Switch function with such an approach breaks down delegation on SP, so it works fine with lists with just over 1000 lines, and breaks down on list with over 4k lines. The functions work fine if I split them to not use Switch or If after Filter - however with that the code grows quite significantly with no apparent need. Any way to do it better? Can I somehow store datasource as variable within the tool (it's not environmental so env variable will not work I think)

Categories:

Building Power Apps

I have the same question (0)

All responses (7)

Answers (1)

Suggested answer

jpespena 335 on at

Like (0)

Report

Hi,

If your lists may grow more than 2000 lines, I suggest you store the values in a collection to manage the delegation issue.

Here's a great article by Matthew Devaney on different ways on how you can store lists into collections that can exceed more than 2000 lines:

https://www.matthewdevaney.com/create-power-apps-collections-over-2000-rows-with-these-4-tricks/

Was this reply helpful? Yes No
Verified answer

WarrenBelz 153,075 Most Valuable Professional on at

Like (0)

Report
Hi JL-14111105-0

You are asking the data source to deal with dynamic source list possibilities within the filter and it will not do this. Try this as it will only send the correct single delegable query (you also need to get the Count out of the Filter sequence).
With( { _Data: Switch( varRegion, "Region1", Filter( List1, field1 = var1 && field2 = var2 ), "Region2", Filter( List2, field1 = var1 && field2 = var2 ), "Region3", Filter( List3, field1 = var1 && field2 = var2 ), "Region4", Filter( List4, field1 = var1 && field2 = var2 ) ), CountRows(_Data) ) )

Please click Does this answer your question if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it a Like.
MVP (Business Applications) Visit my blog Practical Power Apps Buy me a coffee

Was this reply helpful? Yes No
Suggested answer

SaiRT14 1,990 Super User 2025 Season 2 on at

Like (0)

Report
check out the following:

Use a collection to dynamically store and select data sources.

Consider preprocessing large datasets with Power Automate.

Optimize filters for delegation compliance to handle larger datasets.

Avoid heavy Switch or If logic directly in Filter.

Was this reply helpful? Yes No
IAmIntractable 278 on at

Like (0)

Report

@jpespena 2000 does not apply to the number of rows in the list, its the number of rows returned. As long as the rows in the resultset is <=2000 then no issues.

Was this reply helpful? Yes No
WarrenBelz 153,075 Most Valuable Professional on at

Like (0)

Report

IAmIntractable - that is not true in the case of the initial posted code as a Switch/If statement inside a Filter renders it non-delegable (hence the suggested structure I posted)

Was this reply helpful? Yes No
jpespena 335 on at

Like (0)

Report

Hi @IAmIntractable! Which is why I suggested to store data to collections because as Matthew Devaney said in the article I shared below, functions can only return as many records as delegation limit allows which is 2000. If your list go beyond 2000 records, it will only return the first 2000 records but there are ways to store all your 2000+ records into a collection as shown in the article.

But I also think Warren's suggestion solve the problem as well.

Was this reply helpful? Yes No
IAmIntractable 278 on at

Like (0)

Report

@jpespena

Let's clarify your statement "If your list go beyond 2000 records, it will only return the first 2000 records":

1) This is only true if you are pulling all rows from a list that exceeds the power apps imposed limit 500 to 2000 items.

2) If you filter the list using delegable functions, then all of the rows in the list will be searched and only first 2000 (500 to 2000) matches will be returned.

My apps filter lists with 100,000 + items and the results are always under the imposed limit. To achieve this, you have to plan your filtering.

The delegation error is appearing in the original post because Count() is not delegable. Filter into a collection, then account the rows in the collection. Or use the With example below. Note, the with() will alway hide the delegation warning so you may not be aware it's happening.

ClearCollect(col, Filter(Switch(varRegion, "Region1", List1, "Region2", List2, "Region3", List3, "Region4", List4), field1 = var1 && field2 = var2).ID);

Set(nRows, Countrows(col));

Was this reply helpful? Yes No