cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

AddColumns Slowing Down Gallery Filter

Hello!

 

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?

 

Thanks!

5 REPLIES 5
GarethPrisk
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
Eelman
Community Champion
Community Champion

@Anonymous 

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.

Anonymous
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?

 

Thanks!

Anonymous
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?

 

Thanks!

 

Eelman
Community Champion
Community Champion

@Anonymous 

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

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,819)