cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Margaryta
Helper I
Helper I

Expression nested too deep issue

Hi everyone.

I have a gallery that I'd like to filter by multiple columns (in their different combinations), thus I have several input controls. 

 

The Items property of the gallery looks like this:

 

Sort(
     If(Not(IsBlank(Emp.Text)) && Not(IsBlank(Con.Text)) && IsBlank(Dep.Text) && _selectedCount=2 && wm_1_filter =1,
    Filter('[dbo].[welcome_meetings_view]','Start Date'>=sel_start.SelectedDate && 'Start Date'<=sel_end.SelectedDate && Emp.Text in Employee && Con.Text in country && wm_date_1<>Blank()),

    If(
    IsBlank(Emp.Text) && IsBlank(Con.Text) && IsBlank(Dep.Text) && _selectedCount<1 && wm_1_filter =0,
    '[dbo].[welcome_meetings_view]',

    If(Not(IsBlank(Emp.Text)) && IsBlank(Con.Text) && IsBlank(Dep.Text) && _selectedCount<1 && wm_1_filter =0,
    Search('[dbo].[welcome_meetings_view]', Emp.Text, "Employee"),

...............some more IFs....................
)))))))),
    'Start Date',SortOrder.Ascending)

 

At some point I started getting an error: Expression nested too deep. An expression in your formula is nested more than 50 levels.

Is there a chance to avoid this error or to filter the gallery another way? Thanks in advance.

1 ACCEPTED SOLUTION

Accepted Solutions
RandyHayes
Super User
Super User

@Margaryta 

Hard to tell without the complete formula.

In general, you should have 1 table returning function in your formula.  Use the criteria of the Filter to base your logic.  Lot's of If's in an Items formula will lead to pain.

 

Ex.

Sort(
    Filter('[dbo].[welcome_meetings_view]',
        (IsBlank(Emp.Text) || Emp.Text in Employee) &&
        (IsBlank(Con.Text) || Con.Text in country) &&
        (wm_date_1 = If(wm_1_filter=1, Blank(), "somethingElse")) &&
         etc...
    ),
    'Start Date', Ascending
)

 

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

1 REPLY 1
RandyHayes
Super User
Super User

@Margaryta 

Hard to tell without the complete formula.

In general, you should have 1 table returning function in your formula.  Use the criteria of the Filter to base your logic.  Lot's of If's in an Items formula will lead to pain.

 

Ex.

Sort(
    Filter('[dbo].[welcome_meetings_view]',
        (IsBlank(Emp.Text) || Emp.Text in Employee) &&
        (IsBlank(Con.Text) || Con.Text in country) &&
        (wm_date_1 = If(wm_1_filter=1, Blank(), "somethingElse")) &&
         etc...
    ),
    'Start Date', Ascending
)

 

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!

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (3,691)