Forum Discussion
Solved
DAX Measure - Count If
I have the below data loaded in PBI.
| Station | Full name | Employee ID | Average score | Assessment result | Overall Assessment Summary |
| Station 1 | Jayesh Thundathil | 11864 | 64% | Competent | |
| Station 2 | Jayesh Thundathil | 11864 | 100% | Competent | |
| Station 1 | Nitraz 1 | 2165 | 73% | Competent | |
| Station 2 | Nitraz 1 | 2165 | 65% | Competent | |
| Station 1 | Nitraz 2 | 5161 | 27% | Not competent | |
| Station 2 | Nitraz 2 | 5161 | 94% | Competent | |
| Station 1 | Nitraz 3 | 3151 | 55% | Not competent | |
| Station 2 | Nitraz 3 | 3151 | 59% | Not competent | |
| Station 1 | Nitraz 4 | 15461 | 64% | Competent | |
| Station 2 | Nitraz 4 | 15461 | 100% | Competent | |
| Station 1 | Nitraz 5 | 36456 | 64% | Competent | |
| Station 2 | Nitraz 5 | 36456 | 47% | Not competent | |
| Station 1 | Nitraz 6 | 7894 | 73% | Competent | |
| Station 2 | Nitraz 6 | 7894 | 0% | Not competent | |
| Station 1 | Nitraz 7 | 6549 | 36% | Not competent | |
| Station 2 | Nitraz 7 | 6549 | 47% | Not competent | |
| Station 1 | Nitraz 8 | 3652 | 82% | Competent | |
| Station 2 | Nitraz 8 | 3652 | 82% | Competent | |
| Station 1 | Severine 1 | 12345 | 55% | Not competent | |
| Station 2 | Severine 1 | 12345 | 65% | Competent |
The criteria for the overall assessment summary is that an employee is 'Competent' if he/she is competent in both station 1 and station 2, if not he/she is 'Not competent'. Basically, I want to count number of 'Competent' for each employee and if it equals to 2, marks the employee as 'Competent' and if less than 2, then marks the employee as 'Not competent'.
How to proceed to write this measure? I would like to create a chart to show the overall assessment summary as shown below.
5 Replies
Hello Nitraz,
You can try something like this:
In plain text for convenience:
Measure = COUNTX ( FILTER ( ADDCOLUMNS ( VALUES ( data[Full name] ), "Counter", VAR _CurrentName = [Full name] RETURN COUNTX ( FILTER ( data, [Full name] = _CurrentName && [Assessment result] = "Competent" ), [Full name] ) ), [Counter] = 2 ), [Full name] )Best Regards,
Alexander
