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

Announcements

News and Announcements icon
Community site session details

Community site session details

Session Id :
Power Platform Community / Forums / Power Apps / Filter a gallery of on...
Power Apps
Answered

Filter a gallery of one SharePoint list based on an ID count of a second list

(0) ShareShare
ReportReport
Posted on by 30

Scenario: I am trying to filter a gallery called Shifts (a list stored in SharePoint), based on how many times a 'Shift ID' is present in Requests (a second SharePoint list). My Requests list has a lookup column that stores corresponding Shift IDs.

End result: I'm trying to only show my users all Shifts that have fewer than 10 Requests.

I know I can use CountIf to determine if a particular Shift ID is present in the Results list more than X times. My initial attempt was to filter the gallery of Shifts with the following:

 

 

Filter(Shifts, (CountIf(Requests, 'Shift ID'.Id = ID) < 10))

 

 

 

Unfortunately, that scenario failed, as have attempts around building a collection and trying to store those unique IDs there.

Here's an example of how my lists are setup and which IDs I would want filtered out of the Shifts gallery. In this example, I am only wanting to show Shifts that have not been requested more than 2 times, so Shift ID 2 should be filtered out from the Shifts gallery.

Thanks for any advice on this!

Requests

IDEmployee ID (lookup)Shift ID (lookup)Status
112Open
241Open
322Open
463Open


Shifts

IDDateTimeLocation
14/22/20203a-4pCentral
24/22/202012a-6pMain
34/23/20206a-2pSouth




Categories:
  • WarrenBelz Profile Picture
    156,940 Most Valuable Professional on at

    Hi @CorbnDallas ,

    For the solution below, I have to suggest you change something. You cannot Sort or Group by a Lookup field and Filtering is also problematic. If you can change this to a text field, or have a "shadow" text field, then I am suggesting a gallery using the GroupBy function to get the user requests in one line, then drilling into the table that results from the rest of the data to count the number of Requests present, then filtering this for results less than ten. So the gallery Items would be something like this

    Filter(
     GroupBy(
     Shifts,
     "ShiftID", //New Column suggested
     "RemainData" //You can call this whatever you want
     ),
     CountRows(RemainData.Requests) < 10
    )

    If you cannot do this, I think your desired result will be very challenging if not unachievable.

     

    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.

     

  • CorbnDallas Profile Picture
    30 on at

    Thanks @WarrenBelz,

    I will try to implement your suggestion today. Can you confirm for me, that the new "shadow field" would reside in the Shifts list? So adding a new Shift ID (text value) column to match the auto-generated ID field in the shifts table. Correct?

    I had considered adding a new column to my Shifts list called NumRequests.

    That field could increment by one (via Flow) each time a new request is made for a particular shift. Then it would just be a matter of filtering the shifts gallery based on the number in that column.

    My only hesitation with that idea was that I wanted to avoid scenarios where my users might be trying to update the same SharePoint list item at the same time.

  • Verified answer
    WarrenBelz Profile Picture
    156,940 Most Valuable Professional on at

    Hi @CorbnDallas ,

    Firstly I forgot to mention how to display the field in the gallery - a label with the Text

    CountRows(ThisItem.RemainData.Requests)

    Now to your questions - I will deal with the SharePoint user question first. If you are going to let your users loose in a Data Sheet Quick Edit view, then Choice /Lookup columns are probably needed, but anything else can be done in Power Apps, either integrated or stand alone. I do not use SharePoint Lookup columns at all and very few Choice columns. If you keep your base data to Text and Numeric fields (you will need some Date as well, but they are not Delegable), almost all of your Power Apps data management issues go away. You also do not generally need a Flow to do make data updates if is only updated in Power Apps.

    You can easily have an OnChange event on your Lookup Field and reset the shadow field (it will need to be on the form but can be hidden) with the Default

    YourLookupControlName.Selected.Value

    You could also write a number field to the incremental value by looking up the existing number in a Variable, adding 1 to it and resetting the control with a Default of the Variable.

    GroupBy is still a good and simple tool for summarizing data.

     

    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.

  • WarrenBelz Profile Picture
    156,940 Most Valuable Professional on at

    Hi @CorbnDallas ,

    Just checking if you got the result you were looking for on this thread. Happy to help further if not.

     

    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.

  • CorbnDallas Profile Picture
    30 on at

    Hello @WarrenBelz ,

     

    Yes I did get the result I was looking for. Thanks for your help!

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

Quick Links

Season of Sharing Community Challenge Winners!

Congratulations to our community stars!

Kudos to our 2025 Community Spotlight Honorees

Expanding mentorship, skilling, and AI innovation

Leaderboard > Power Apps

#1
11manish Profile Picture

11manish 248 Super User 2026 Season 2

#2
WarrenBelz Profile Picture

WarrenBelz 192 Most Valuable Professional

#3
Mohsin Ali Profile Picture

Mohsin Ali 135

Last 30 days Overall leaderboard