Showing results for 
Search instead for 
Did you mean: 
Not applicable

AddColumns Slowing Down Gallery Filter



I am using AddColumns to my data set based with a lookup to another dataset in order to set colors/filters in a gallery for the first dataset.  This has massively slowed down the functionality of the app and for any filter applied, the app takes multiple minutes to display that filter on the gallery.  I'm looking for any ideas of another way to meet my goal or how to make AddColumns be more efficient?


Dataset A:  List of employee information (name, department, employee number, cell phone...etc._)

Dataset B:  List of employee responses to monthly survey - so each employee will have multiple responses within the data set over time but it's the same set of questions each month


The gallery is of Dataset A (list of employees) but within the card I want to show a status based on a response from the LATEST response from the employee in Dataset B. 


I was able to achieve this by adding a column to Dataset A with a Lookup(Last(Filter(....))) type of formula on dataset B.  I get the calculation I need in the new columns (I checked them by adding a label on the gallery) but when I filter on those additional columns or any other columns in the gallery it takes MINUTES to calculate every time.


Any ideas on another way I can achieve this?  Or make the AddColumns function more efficient?



Resident Rockstar
Resident Rockstar

What you're describing will perform poorly in most scenarios. Basically it's an N+1 data call. Meaning, each row (N) is performing the operation (+1), which is itself relatively slow.

If you can, I recommend collecting the tables into the app, and then functions like this will be basically instant.

This assumes that your data

  • Doesn't exceed delegation limits (or you are willing to overcome them)
  • Will work with "stale data" - meaning the collection was made at X time, and isn't updated unless you specifically update it
Not applicable


A method I'd use would be to create a CompletedSurveyID each time a person completes the survey and store this in both lists. You'd then have a reference you could easily filter on without having to use Last() or AddColumns().


I can help further with this if you are unsure what I mean.

Not applicable

Thanks for the recommendation!  Would you achieve this through power automate?  Like when a survey response is added, power automate updates a column on the master employee list ("Last Survey ID" or something like that) of the SharePoint ID of the survey response?  Or is there a way to do this from SharePoint?



Not applicable

Thanks for the recommendation!  Do you recommend I do the AddColumns to the App OnStart menu so it's slow to load but then there's that static data set throughout the app?  And maybe add a button so the user can choose to refresh?




Not applicable


It's a little difficult to answer this because I'm unsure of how your system is setup.


Are you using a MS Form for your Survey which then adds data to a SP List? If so, how does the Form know who the person is? Do you use an email address for this? If so, you can use the email address in Power Automate to filter the Master Person List and add a LastSurveyID and also add this to the Survey list. You could do this with a variable that concatenates Person_Date_Time ... or ... use a GUID ... or any other relevant string/int that works for you.


If people are completing the MS Form anonymously then I cannot see how you'd identify who they are because you'd be relying on them to either complete their name correctly (they never do, haha) or their email address correctly, which can also fail in my experience.

Helpful resources

Power Apps News & Annoucements carousel

Power Apps News & Announcements

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

Community Call Conversations

Introducing the Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

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 (4,358)