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
| ID | Employee ID (lookup) | Shift ID (lookup) | Status |
| 1 | 1 | 2 | Open |
| 2 | 4 | 1 | Open |
| 3 | 2 | 2 | Open |
| 4 | 6 | 3 | Open |
Shifts
| ID | Date | Time | Location |
| 1 | 4/22/2020 | 3a-4p | Central |
| 2 | 4/22/2020 | 12a-6p | Main |
| 3 | 4/23/2020 | 6a-2p | South |