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

Notifications

Announcements

Community site session details

Community site session details

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

Filter on a Dataverse Many-to-Many relationship

(0) ShareShare
ReportReport
Posted on by 5
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)  
        )
    )
)
 
I have the same question (0)
  • Verified answer
    Ravi-Prajapati Profile Picture
    416 Super User 2025 Season 2 on at
    try this
    ClearCollect(
        StudentsOnRegister,
        ForAll(
            'ComboBox-Class'.SelectedItems,
            ShowColumns(
                Filter(
                    Contacts,
                    Contact in Distinct(cus_Class_Contact_Students, Contact)
                ),
                "Contact"
            )
        )
    );
    // Get the unique students from the collection
    ClearCollect(
        UniqueStudentsOnRegister,
        Distinct(StudentsOnRegister, Contact)
    );
    // Count the distinct number of students
    CountRows(UniqueStudentsOnRegister)
     

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

Forum hierarchy changes are complete!

In our never-ending quest to improve we are simplifying the forum hierarchy…

Ajay Kumar Gannamaneni – Community Spotlight

We are honored to recognize Ajay Kumar Gannamaneni as our Community Spotlight for December…

Leaderboard > Power Apps

#1
WarrenBelz Profile Picture

WarrenBelz 765 Most Valuable Professional

#2
Michael E. Gernaey Profile Picture

Michael E. Gernaey 343 Super User 2025 Season 2

#3
Power Platform 1919 Profile Picture

Power Platform 1919 272

Last 30 days Overall leaderboard