Counting Rows with Conditions in Measure

[NSBS]

Hi PowerBI Community,

Need some help in creating a DAX measure code for below case.

Considering below attempt # and its status for each person, and the Overall Status will be consided as "Passed" if there is any "Passed" attempt (within 3 attempts).
Now I need to create a mesure to directly calculate how many person has "Passed" overall status

Notes: the Overall Status column is not exist in my data and I don't want to create one, instead I want to create a measure to directly count the number of "Passed" Overall Status

Name	Attempt	Attempt Status	Overall Status
Carol	1	Failed	Passed
Carol	2	Failed
Carol	3	Passed
Jane	1	Passed	Passed
Jane	2	Passed
Jane	3	Passed
Adam	1	Failed	Failed
Adam	2	Failed
Adam	3	Failed

[v-jianboli-msft · Accepted solution]

Hi @NSBS ,

Please try:

Count Passed = 
var _a = ADDCOLUMNS(ALL('Table'),"Overall", IF("Passed" in SELECTCOLUMNS(FILTER('Table',[Name]=EARLIER('Table'[Name])&&[Attempt]<=3),"Attempt status",[Attempt Status]),
"Passed","Failed"))
return CALCULATE(DISTINCTCOUNT('Table'[Name]),FILTER(_a,[Overall]="Passed"))

Final output:

Best Regards,

Jianbo Li

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

[NSBS]

Kindly refer attached screenshot for clearer table