cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MKrezymon
New Member

Delegation Warning with IF statement in Filter() function

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.

1 ACCEPTED SOLUTION

Accepted Solutions
RandyHayes
Super User
Super User

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

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

2 REPLIES 2
RandyHayes
Super User
Super User

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

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

Hi @MKrezymon @RandyHayes 

 

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

 

Thanks

 

Nigel

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

365 EduCon 768x460.png

Microsoft 365 EduCon

Join us for two optional days of workshops and a 3-day conference, you can choose from over 130 sessions in multiple tracks and 25 workshops.

Users online (3,549)