Showing results for 
Search instead for 
Did you mean: 
Level: Powered On

CountIf: Reference field from enclosing filter statement

I have two SharePoint lists as my data sources. One of the list holds events that have a date column and the other one holds the actual participants referring to those events -> 1:m relationship. Additionally the events list also has a column "MaxCount" denoting how many people can attend the event.
In my PowerApp I provide my clients with a gallery where they can choose which event they want to attend to. Now I would like to filter out all event items in the gallery where the "MaxCount" has been reached. I tried something like this:

Filter('Events'; ActiveEvent.Value = "Yes" && CountIf("Participants"; EventID = ID) < MaxCount)

Where ID is the SharePoint list item ID of the event item I use in the participants list to reference my events. When thinking about it, it could not work, because CountIf does not know about the surrounding Filter call, right? Is there a way I can reference the "outer" ID? Is there another way to achieve my goal?
I though of displaying the event and when trying to apply for it I would give out an error, but that is not very user friendly.
Thank you for your help.

Level: Powered On

Re: CountIf: Reference field from enclosing filter statement

A possible solution:


I created another column "EventID" on my event list. Since you cannot use "ID" field in calculated columns, I currently assign the ID manually. Although this error-prone it is actually quite simple to handle.

Level 10

Re: CountIf: Reference field from enclosing filter statement

#1. Create a collection having Participant as source and use GroupBy function and display EventID and Participation Count. Also have a button 'Attend' event (for participant to register for an event)


#2 Also create a collection having EventID and MaxCount.  


In your gallery with the particiaption collection - #1  enaable the button if participation count <= maxcount

Helpful resources


Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!


Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020


Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors
Users Online
Currently online: 247 members 6,665 guests
Please welcome our newest community members: