Showing results for 
Search instead for 
Did you mean: 
Frequent Visitor

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.

Frequent Visitor

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.

Not applicable

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

Community Conference

Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

Watch Now

Experience what’s next for Power Apps

See the latest Power Apps innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.


Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (5,083)