DAX Measure - Count If

[Nitraz]

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.

[barritown]

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

My YouTube vlog in English

My YouTube vlog in Russian

[Nitraz]

Hi @barritown 

Thanks for creating the measure to show who are 'competent' but I need to show who are 'not competent' as well in the same chart as shown below. How to proceed?

[ryan_mayu · Accepted solution]

@Nitraz 

pls see the attachement below

[Nitraz]

Thanks @ryan_mayu 

[ryan_mayu]

you are welcome