Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
Solved! Go to 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.
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.
Kindly refer attached screenshot for clearer table
User | Count |
---|---|
93 | |
83 | |
77 | |
73 | |
66 |
User | Count |
---|---|
115 | |
103 | |
93 | |
64 | |
61 |