DAX query: trying to count unique entries that match two different criteria

Question

I have a data table I've imported into PowerBI from an csv that is a large list of exam results. Each row represents the details of when that particular exam was taken by a particular student. Some students will have taken the same exam more than once resulting in multiple rows for the same student/subject.

SUBJECT	STUDENT	DATE
English	Fred	Feb
Maths	Stuart	May
Science	Fred	Feb
Maths	Simon	Mar
Science	Peter	June
English	Peter	June
English	Fred	Oct
Maths	Paul	June
Maths	Fred	Jan
English	Stuart	June

I'm trying to find a query that will return a count of the unique number of students that have taken a particular combination of subjects. So, for the example above, how many students have taken both English **and **Maths?

The correct return would be 2 as only fred and stuart have taken both subjects.

I'm very new to DAX and have tried nesting a range of different statements but just can't figure this out? Nothing seems to get even close to the result needed.

Answer 1

If your combination is static, you can use the following measure :

Students English Maths Count = 
VAR EnglishStudents = 
    CALCULATETABLE (
        DISTINCT ( TableTest[STUDENT] ),
       TableTest[SUBJECT] = "English"
    )
VAR MathsStudents = 
    CALCULATETABLE (
        DISTINCT ( TableTest[STUDENT] ),
        TableTest[SUBJECT] = "Maths"
    )
VAR StudentsBoth = 
    INTERSECT ( EnglishStudents, MathsStudents )

RETURN
    COUNTROWS ( StudentsBoth )

If you need something dynamic, I would go for creating two slicers to select Subject A and Subject B :

Create two seperate tables with the unique values of subjects :

UniqueSubjectsA = DISTINCT(TableTest[SUBJECT])

UniqueSubjectsB = DISTINCT(TableTest[SUBJECT])

Your model is like below :

Your dynamic measure :

Dynamic Students Count = 
VAR Subject1Selection = SELECTEDVALUE(UniqueSubjectsA[Subject])
VAR Subject2Selection = SELECTEDVALUE(UniqueSubjectsB[Subject])
VAR StudentsSubject1 = 
    CALCULATETABLE(
        DISTINCT('TableTest'[STUDENT]),
        'TableTest'[SUBJECT] = Subject1Selection
    )
VAR StudentsSubject2 = 
    CALCULATETABLE(
        DISTINCT('TableTest'[STUDENT]),
        'TableTest'[SUBJECT] = Subject2Selection
    )
VAR IntersectionStudents = INTERSECT(StudentsSubject1, StudentsSubject2)

RETURN COUNTROWS(IntersectionStudents)