Power Platform Community Forum Thread Details

I have have read all the articles and question.

However, I cannot find to change my filter condition to remove the delegation issue.

My formula in gallery items is:

Filter(Properties,StartsWith(ID,TextInput1_4.Text),TextInput1_3.Text in Address,Substitute(Dropdown1.SelectedText.Value,"All Leaves","") exactin PropertyStatus)

My table has only 4 data record.

Does anyone has any suggestion, how can I optimise the formula?

Categories:

Building Power Apps

I am assuming that your table is going to grow beyond four records otherwise you don't really need to worry about delegation. If your table will not grow beyond 500 records (and is relatively static) you could just load the data into a collection when your App starts and query against the collection instead of the table.

If your table will grow beyond 500 records and/or the data is not static within a session then you can look at nesting your filter clauses. Your filter has a combination of delegable (StartsWith) and non-delegable (in) clauses so you want to nest any delegable clauses. If you leave all your clauses in one Filter (i.e. do not nest the your expression with the delegable part inner-most), it makes the whole filter non-delegable.

Filter(

Filter(Properties,StartsWith(ID,TextInput1_4.Text),

TextInput1_3.Text in Address,Substitute(Dropdown1.SelectedText.Value,"All Leaves","") exactin PropertyStatus)

The innermost part of the Filter (in italics) is delegable and will return only the matching records. The outer part of the formula (non-delegable) will then filter just the matching records within PowerApps. Assuming the StartsWith has a high degree of selectability, this may be enough to get you 'over the line' in terms of performance (though you will still see the delegation warning). For example, you have 3,000 records but StartsWith will narrow that down to 300 which will be returned to PowerApps where the remaining clauses will then be applied.

If StartsWith is not selective enough with one character, you could put in logic such that the filter will not fire until you have entered at least 2 or 3 or however many characters are likely be selective enough that you can be confident the number of returned values will always be below the delegable limit (500 to 2000 depending on your settings, but try to avoid going beyond 500 as this often results in performance issues).

There are also ways of replacing an 'in' clause with a series of 'OR's though this does get somewhat complex.