The basic premise I have is a school with multiple students attending multiple classes, and I want to filter on this N:N relationship to calculate a distinct number of students in any group of classes. (I can manage this via FetchXML in PowerAutomate and on the Model-Driven app, but not in a custom page/canvas app.)
I have 1) a table of contacts (inc. the students), 2) a table of classes, and 3) an N:N relationship called cus_Class_Contact_Students
(An extra layer of complication is a N:1 relationship to between Class and Department, but which, ideally, I would like to filter, once I crack the first part.)
My aim is to find a way to calculate the distinct number of students - ideally dynamically from a ComboBox of departments.
I have managed to get a result for the number of students in a single selected class (based on a Gallery of the classes I want filtered):
CountRows(
Filter(
Contacts,
Contact in Distinct('Gallery-Class'.Selected.cus_Class_Contact_Students, Contact)
)
)
But I have not succeeded in running this through a ForAll using a ComboBox (or indeed a hardcoded Filter) of the classes. I've tried collecting data into a collection to count rows, using the two options below. But neither work.
ForAll(
'ComboBox-Class'.SelectedItems,
Collect(StudentsOnRegister,
Filter(
Contacts,
Contact in Distinct(ThisRecord.cus_Class_Contact_Students, Contact)
)
)
)
Or replacing ThisRecord with an As
ForAll(
'ComboBox-Class'.SelectedItems As ClassIteration,
Collect(StudentsOnRegister,
Filter(
Contacts,
Contact in Distinct(ClassIteration.cus_Class_Contact_Students, Contact)
)
)
)