Delegation Warning with IF statement in Filter() function

[MKrezymon]

Hi Power Community!

During building my last PowerApp I have encountered a problem that is quite painful because of the size of my dataset. I need to implement filters that simply filter the data source (SQL view) if cmbFilter.Selected is not blank, so firstly my code looked like this:
Filter(
'view_SampleData',
If(
cmbFilter.Selected.Result = Blank(),
true,
Result = cmbFilter.Selected.Result)
)
With formula like this I got Delegation Warning, so I tried to rebuilt it and I noticed that once I excluded IF statement from Filter() function the warning disappeared, so I ended with such a formula:
If(
IsBlank(cmbFilter.Selected.Result),
'view_SampleData',
Filter(
'view_SampleData',
Result = cmbFilter.Selected.Result)
)

The problem is that such a formula is hard to maintain if I have more filters, e.g. 5 filters. Do you have any ideas how can I handle this better?

Note: I have to filter data from data source, because if I Collect() data at app start and I adjust filters on created collection then I may not find the record that I am looking for, as Collect function will import only part (max 2000) records that may not include this particular record. I know that such a risk will still be there in case with filters adjusted I have still >2000 results, but at least this risk lower.

[RandyHayes · Accepted solution]

@MKrezymon 

Skip the collection!

Your formula should be:

Filter('view_SampleData',
    (IsBlank(cmbFilter.Selected.Result) || Result = cmbFilter.Selected.Result) 
)

Always avoid If statements in filter criteria.  You will get delegation warnings! 

As for additional filters, just add them in the same style.

Ex.

Filter('view_SampleData',
    (IsBlank(cmbFilter.Selected.Result) || Result = cmbFilter.Selected.Result) &&
    (IsBlank(cmbOtherFilter.Selected.column) || dbField = cmbOtherFilter.Selected.column)
)

I hope this is helpful for you.

[NigelP]

Hi @MKrezymon @RandyHayes 

What about if I have a "--All--" in my dropdown ? In which case I want everything displayed.

Thanks

Nigel

[_Nick_]

I've been trying to figure out how to do this for 2 days until I finally stumbled upon your perfect solution. Thanks!