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:

Using Apps Using Formulas

All responses (6)

Answers (0)

jpespena 294 on at

Like (0)

Report

Delegation on functions with switch

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.
WarrenBelz 143,591 on at

Like (0)

Report

Delegation on functions with switch

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)
IAmIntractable 164 on at

Like (0)

Report

Delegation on functions with switch

@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.
Suggested answer

SaiRT14 1,334 on at

Like (0)

Report
Delegation on functions with switch

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.
WarrenBelz 143,591 on at

Like (0)

Report
Delegation on functions with switch

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
Suggested answer

jpespena 294 on at

Like (0)

Report

Delegation on functions with switch

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/