web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

Agent Academy Hackathon is happening now!
Welcome to the Power Platform Communities
News and Announcements icon
Community site session details

Community site session details

Session Id :
Power Platform Community / Forums / Power Apps / Does delegation extend...
Power Apps
Answered

Does delegation extend to nested filters?

(0) ShareShare
ReportReport
Posted on by Community Power Pla... Microsoft Employee

Hi, just want to ensure I correctly understand how delegation works with nested filters. 

I have the following code for the items property of a gallery:

objectivelyLost_0-1609720639595.png

TL_ShowComplete is a Checkbox and SOWTimeline is a Sharepoint list, which currently only has 20 or so records, but I expect it to have upwards of 50k eventually.  The formula works as expected, but is obviously throwing a delegation warning.  My assumption here is that I'm ok, because the Switch function is only acting on the outer filter, while the inner filter is the one acting on the SP list.  I do not ever expect the inner filter to return more than 50 or so results.

 

Am I thinking about this correctly?  I'd rather not ClearCollect() it if it can be avoided. 

thanks!

 

Categories:
Building Power Apps
I have the same question (0)
  • Drrickryp Profile Picture
    Drrickryp Super User 2024 Season 1 on at

    Hi @Anonymous 

    I believe that your Switch function is showing a delegation error because TL_ShowComplete is a yes/no field in SharePoint.  To test if I am right, change the column types in SharePoint from yes/no to single line of text and leave everything else exactly as it is and see if the delegation warning disappears. 

    For the exact answer to your question, it would be "that depends".  I refer you to @WarrenBelz 's excellent blog post on delegation in SharePoint. https://warrenbelz.blogspot.com/2020/08/power-apps-delegation-is-word-not.html  for further discussion. 

  • Verified answer
    Pstork1 Profile Picture
    Pstork1 69,481 Most Valuable Professional on at

    You are correct.  This is one of the primary strategies that a lot of people use to work with Delegation.  Have an inner filter that is delegable that will shrink the overall data set to a size below the data row limit and then apply non-delegable functions to that result.

     

    One suggestion.  If you are going to use an inner and outer filter its usually a good idea to setup a label that will display if CountRows of the inner filter is ever equal to or greater than the data row limit.  That label can have a warning that you may be missing data due to delegation.  In your case that would look something like this in the display property of the label.

    If(CountRows(Filter(SOWTimeLine, SOWID = SOWMasterItem.ID and Hidden = False))>= 500,true, false)

     

  • WarrenBelz Profile Picture
    WarrenBelz 155,374 Most Valuable Professional on at

    Hi @Anonymous ,

    I will throw my 20c worth in - this should take care of the Delegation (as long as the With() filter returns less than your Delegation limit. I think I have the bottom part right, but not sure

    With(
 {
 wSOW:
 Sort(
 Filter(
 SOWTimeLine,
 SOWID=SOWMasterID && !Hidden
 ),
 DueDate
 )
 }
 Filter(
 wSOW,
 Completed = !TL_ShowcaseComplete
 )
)

     

    Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

  • Jeff_Thorpe Profile Picture
    Jeff_Thorpe 6,085 Super User 2024 Season 1 on at

    I might be missing something but can't the Filter() formula be tweaked to be delegable and not use the Switch()? 

    I was thinking something like this:

     

    Sort( 
	Filter( 
		SOWTimeLine, 
		SOWID = SOWMasterItem.ID And Hidden = false And ((TL_ShowComplete.Value = false And Completed = false) Or TL_ShowComplete.Value = true)
	),
	DueDate , 
	Ascending 
)

     

  • Community Power Platform Member Profile Picture
    Community Power Pla... Microsoft Employee on at

    Hi @Drrickryp,

    TL_ShowComplete is actually just a checkbox object in Powerapps, "Completed" two lines below is the SP field, and it is indeed a yes/no field.  I tried changing it in SP along with false -> "false" in the formula, but still get the same error. 

     

    Thanks for the link! Bookmarked for later!

  • Community Power Platform Member Profile Picture
    Community Power Pla... Microsoft Employee on at

    @Jeff_Thorpe 

    Sorry, I wasn't very clear.  TL_ShowComplete is a checkbox user input in Powerapps. The SharePoint list is being filtered by the inner filter first and, the outer one is filtering down to items that have {Completed: false}, when TL_ShowComplete is unchecked in the app (and vice versa of course).

  • Community Power Platform Member Profile Picture
    Community Power Pla... Microsoft Employee on at

    Hi @WarrenBelz 

    Thanks for your help once again. You're everywhere!

     

    I didn't do a very good job of explaining what I was trying to do... I need the filter to show me records with {Completed: false} when TL_ShowComplete is unchecked in the app, and {Completed: true AND false} when it is checked, hence the Switch().   I tried your solution out anyway though and found that it gave me a different delegation warning:

    objectivelyLost_0-1609748549488.png

     

    I think I'm ok based on Psork1's response, but it was interesting to me that putting the filter inside of the With() function caused the warning where there was none before so I thought I would share.

  • Community Power Platform Member Profile Picture
    Community Power Pla... Microsoft Employee on at

    @Pstork1 Thanks for the tip!  In this case, I'm very certain that there won't be anywhere near 500 records, as the user would have to enter them all manually, but I will definitely be using that warning label in other areas.

  • WarrenBelz Profile Picture
    WarrenBelz 155,374 Most Valuable Professional on at

    Hi @Anonymous ,

    Yes, danger of free-typing without really thinking it through properly - I was trying to get a Delegable data set and forgot about the Boolean in it. This should do the job

    With(
 {
 wSOW:
 Sort(
 Filter(
 SOWTimeLine,
 SOWID=SOWMasterID
 ),
 DueDate
 )
 },
 Filter(
 wSOW,
 Completed = !TL_ShowcaseComplete && !Hidden
 )
)

     

    You might find this blog of mine on the subject of some use to you.

  • Community Power Platform Member Profile Picture
    Community Power Pla... Microsoft Employee on at

    @WarrenBelz 

    With some tweaks(couple typos and a missing comma after the CurlyClose), that does get rid of the delegation warning, but it still doesn't address my underlying need.  Essentially, I need to check a box in the app to toggle the second filter on and off so that I either filter out completed items or show everything.  

     

    Thanks for the link, I actually came across the same one a little earlier via this blog that @Drrickryp  shared: https://warrenbelz.blogspot.com/2020/08/power-apps-delegation-is-word-not.html

     

    I'll definitely be taking a look though it later on!

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Agent Academy Hackathon is happening now!
Welcome to the Power Platform Communities

Quick Links

Introducing the 2026 Season 1 community Super Users

Congratulations to our 2026 Super Users!

Kudos to our 2025 Community Spotlight Honorees

Congratulations to our 2025 community superstars!

Congratulations to the April Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Power Apps

#1
Vish WR Profile Picture

Vish WR 834

#2
Valantis Profile Picture

Valantis 533

#3
Haque Profile Picture

Haque 410

Last 30 days Overall leaderboard

Featured topics

Auto-Populate field Power Pages
Gallery Tabs - Show or Hide Tabs based on Field Value
How to patch multiple records at once
Enable Copilot for end users in model-driven apps
Question for everyone : How are you using Create Text GPT in AI Builder?
Community content - sample components, blogs etc. - Link to this page (http://aka.ms/PCFdemos)
Welcome to the Power Apps forum!

Product updates

Microsoft Power Platform Community release plans