Filter using a measure with Distinct

[Anonymous]

Hello

I’ve have a table that contains student module data. A student can have multiple modules.  There is a Module_Passed flag that is either 0 or 1

There are measures (see attached code) to count all rows, the distinct number of students, the distinct number of student that have Modules that have not been passed and the distinct number of students have passed all modules.

The issue is when I grab any of the Distinct measures e.g. the measure that counts the number of students that have passed all modules and then include the modules, I get the number for all passes and not just the count for the passed all modules

The pivot grand total is correct, but the values in the body of the pivot are not correct in context

Code and output screen prints below... 

I'm not sure how to fix the context. All suggestions welcome.

row_Count:=COUNTROWS(Table1)

Distinct Student_Count:=DISTINCTCOUNT(Table1[Student_ID])

Students with Modules Not Passed:=CALCULATE (
        DISTINCTCOUNT ( Table1[Student_ID] ),
        FILTER ( Table1, Table1[Module_Passed] = 0 )
    )

Students with All Modules Passed:=[Distinct Student_Count]-[Students with Modules Not Passed]

 

[v-frfei-msft]

Hi @Anonymous,

Create a measure sa below. If it doesn't meet your requirement, kindly share your excepted result to me.

Students with All Modules Passed2 = CALCULATE(DISTINCTCOUNT(Table1[Student_ID]),ALL(Table1))-CALCULATE(DISTINCTCOUNT(Table1[Student_ID]),FILTER(ALL(Table1),Table1[Module_Passed]=0))

Regards,

Frank

[v-frfei-msft]

Hi @Anonymous,

Does that make sense? If so, kindly mark my answer as a solution to close the case.

Regards,
Frank