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

Announcements

News and Announcements icon
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 Moderator 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

Introducing the 2026 Season 1 community Super Users

Congratulations to our 2026 Super Users!

Kudos to our 2025 Community Spotlight Honorees

Congratulations to our 2025 community superstars!

Congratulations to the March Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Power Apps

#1
11manish Profile Picture

11manish 536

#2
WarrenBelz Profile Picture

WarrenBelz 426 Most Valuable Professional

#3
Haque Profile Picture

Haque 305

Last 30 days Overall leaderboard