How to distinct count by category to filter out specific result?

[Anonymous]

Hi guys,

Seeking for advice and help here,
Here are the scenario, I have student name, subject and score. Whichever a individual student have single fail subject consider as fail. The objective is to count the number of student achieve all pass.

Student Name	Subject	Score
Alice	Math	Pass
Alice	Science	Fail
Alice	English	Pass
David	Math	Pass
David	Science	Pass
David	English	Pass
Mike	Math	Fail
Mike	Science	Pass
Mike	English	Fail
Peter	Math	Pass
Peter	Science	Pass
Peter	English	Pass

However, the dax measure i created
CALCULATE(DISTINCTCOUNT(Table[Student Name]), FILTER(Table,Table[Score] <> "Fail")) and this return result = 4 students

The desired outcome is 2 students (David & Peter) with all Pass.

How to derive Dax measure to count unique Student with ALL Score  = "Pass"?

[Anonymous]

Hi @Anonymous ,

Please check the formula:

Measure = 
var tmp = SUMMARIZE('Table','Table'[Student Name],"is_pass",CALCULATE(MIN('Table'[Score]),ALLEXCEPT('Table','Table'[Student Name])))
return
COUNTROWS(FILTER(tmp,[is_pass]="Pass"))

Best Regards,

Jay

[VahidDM · Accepted solution]

Hi @Anonymous 

Try this measure:

Measure = 
Var _Count_Pass_Student = SUMMARIZE('Table','Table'[Student Name],"PASS",CALCULATE(COUNTROWS('Table'),'Table'[Score]="Pass"))
Var _Filter_Just_All_Pass = FILTER(_Count_Pass_Student,[PASS]=3)
return
COUNTROWS(_Filter_Just_All_Pass)

Output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!! 

LinkedIn | Twitter | Blog | YouTube 

[AntonioM]

You could try

Measure = CALCULATE ( DISTINCTCOUNT ('Table'[Student Name] ) - CALCULATE( DISTINCTCOUNT('Table'[Student Name] ) ,'Table'[Score] <> "Pass"))

That will get the total number and subtract however many have at least one Fail