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
Ignite 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Power Apps Africa Challenge 2022

Power Apps Africa Challenge

Your chance to join an engaging competition of Power Platform enthusiasts.

Super User 2 - 2022 Congratulations

Welcome Super Users

The Super User program for 2022 - Season 2 has kicked off!

September Events 2022

Check out all of these events

Attend in person or online, there are incredible conferences and events happening all throughout the month of September.

Users online (3,608)