DAX Measure for Counting Students in a Many-to-Many Relationship
You are modeling a many-to-many relationship between 'Students' and 'Courses' via a junction table 'Enrollments'. You need to create a measure that counts the number of students enrolled in at least one course. The relationship between Students and Enrollments is one-to-many, and between Courses and Enrollments is one-to-many. What DAX measure should you use?
Quick Answer
The answer is DISTINCTCOUNT(Students[StudentID]). This measure correctly counts unique students in a many-to-many relationship because the filter from Courses propagates through the Enrollments junction table to the Students table, and DISTINCTCOUNT ensures each student is counted only once regardless of how many courses they take. On the Microsoft Power BI Data Analyst PL-300 exam, this scenario tests your understanding of how DAX handles many-to-many relationships via bidirectional filtering or cross-filter direction; a common trap is choosing COUNTROWS(Enrollments), which counts enrollments rather than students, or using COUNT(Students[StudentID]), which might include duplicates. The key insight is that DISTINCTCOUNT inherently respects the relationship chain, making it the safest and most accurate choice for counting students enrolled in at least one course. Memory tip: think “DISTINCTCOUNT for distinct people” — when you need a headcount across a bridge table, always reach for DISTINCTCOUNT on the student ID column.
Answer choices
Why each option matters
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
DISTINCTCOUNT(Students[StudentID])
DISTINCTCOUNT(Students[StudentID]). This measure counts unique students, which correctly handles the many-to-many relationship because filters applied to 'Courses' propagate through 'Enrollments' to 'Students', ensuring only students with at least one enrollment are counted. Option A (COUNTROWS(Enrollments)) counts enrollment rows, potentially double-counting students with multiple courses. Option B (COUNT(Courses[CourseID])) counts courses, not students. Option C (COUNTA(Students[StudentID])) counts all non-blank StudentIDs regardless of enrollment, missing the condition. DISTINCTCOUNT is the proper DAX function for distinct counts.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
COUNTROWS(Enrollments)
Why it's wrong here
Counts enrollments, not students.
- ✗
COUNT(Courses[CourseID])
Why it's wrong here
Counts courses.
- ✗
COUNTA(Students[StudentID])
Why it's wrong here
COUNTA counts non-blank values but may not handle filter context as well.
- ✓
DISTINCTCOUNT(Students[StudentID])
Why this is correct
Counts unique students, works with many-to-many.
Go deeper
Related to this question
About these practice questions
Courseiva writes every PL-300 question from scratch — 217 in total, each with an explanation and a wrong-answer breakdown. None are copied from real exams or dumps. Learn why practice questions differ from exam dumps →
Same concept, more angles
3 more ways this is tested on PL-300
These questions test the same concept from different angles. Work through them to make sure you can recognise it however the exam phrases it.
Variation 1. You need to create a relationship between two tables where one table has duplicate values on the key column. Which cardinality should you choose?
easy- A.One-to-one
- B.Many-to-many
- ✓ C.Many-to-one
- D.One-to-many
Why C: Many-to-one cardinality is used when the lookup table (the 'one' side) has unique values and the fact table (the 'many' side) has duplicate values in the key column. This is the typical relationship in a star schema where a dimension table (unique keys) relates to a fact table (duplicate keys). Option A is incorrect because one-to-one requires unique keys on both sides. Option B is incorrect because many-to-many is used when both tables have duplicate values. Option D is incorrect because one-to-many is the reverse relationship (where the other table has duplicates).
Variation 2. When creating a many-to-many relationship between two tables, what is a common approach to model this in Power BI?
easy- A.Use the CROSSJOIN DAX function in calculated tables.
- B.Merge both tables into a single table.
- ✓ C.Introduce a bridge table that contains the unique combinations.
- D.Create a direct many-to-many relationship in the model.
Why C: A bridge table (also known as a junction table) is used to resolve many-to-many relationships by creating two one-to-many relationships from the bridge table to each of the original tables. This allows the relationship to be modeled correctly in Power BI. Option A is incorrect because the CROSSJOIN function creates a Cartesian product of all rows from two tables, which is not a proper way to model a many-to-many relationship and leads to incorrect results. Option B is incorrect because merging both tables into a single table removes the relational structure and may cause data duplication and loss of granularity. Option D is incorrect because Power BI does not support direct many-to-many relationships without using a bridge table or setting up a many-to-many cardinality using a common dimension (which still involves a bridge table in the background). Therefore, introducing a bridge table is the standard approach.
Variation 3. You are modeling data with a many-to-many relationship between 'Student' and 'Class' tables. Which THREE configurations can handle this scenario?
hard- ✓ A.Use CROSSFILTER function to enable many-to-many filtering
- B.Remove duplicate rows from both tables
- ✓ C.Create a bridge table with StudentID and ClassID
- D.Change relationship to one-to-many by creating a unique key
- ✓ E.Use a calculated column to concatenate Class names in the Student table
Why A: Options A, C, and E are correct. Option A uses CROSSFILTER to enable many-to-many filtering. Option C creates a bridge table with StudentID and ClassID, which is the standard approach. Option E uses a calculated column to concatenate Class names in the Student table, useful for limited scenarios. Option B is wrong because removing duplicates loses information. Option D is wrong because changing to one-to-many incorrectly assumes uniqueness.
JA
Written by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
This PL-300 practice question is part of Courseiva's free Microsoft certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the PL-300 exam.