Count based on condition - Completion Status

[Juma1231]

Hi,

I need to create a measure to count employees that completed modules ( module completed if all learnings associated are completed)

EMPLOYEE ID	LEARNING	LEARNING STATUS	MODULE
3421	Microsoft Excel	Complete	Microsoft Office
3421	Microsoft Word	Incomplete	Microsoft Office
1111	Microsoft Excel	Complete	Microsoft Office
2112	Microsoft Excel	Complete	Microsoft Office
2122	Microsoft Word	Complete	Microsoft Office
3222	Business Language	Incomplete	Communication Skills

Target value ( Modules completed): 

Microsoft Office = 3 

Thanks in advance,

[AlexisOlson]

Here's another approach:

COUNTROWS (
    FILTER (
        VALUES ( Table2[EMPLOYEE ID] ),
        CALCULATE ( SELECTEDVALUE ( Table2[LEARNING STATUS] ) ) = "Complete"
    )
)

This works because if there are multiple values, then SELECTEDVALUE returns a blank.

[rbriga]

Try:

SUMX(
VALUES( Tablename[EMPLOYEE] ),
IF(
DISTINCTCOUNT( Tablename[LEARNING] ) =
CALCULATE(
DISTINCTCOUNT( Tablename[LEARNING] ),
KEEPFILTERS( Tablename[STATUS] = "Complete" )
),
1,
BLANK()
)
)

[Juma1231]

Hi @rbriga 

I have tried this. . It seems like it's not working; it's showing a blank result. Any other suggestions . .

[rbriga · Accepted solution]

Silly mistake on my side.

1. Build a support measure:

Courses = 

COUNTROWS('Table')

2. This will return the number of employees who completed all their modules:

SUMX(

VALUES('Table'[EMPLOYEE ID]),

IF([Courses] = CALCULATE([Courses],KEEPFILTERS('Table'[LEARNING STATUS] = "Complete")),1,0)

)

[Juma1231]

Thank you @rbriga !! This is exactly what I was looking for 🙂 

[Juma1231]

Sorry , It should be 3. it was a typo. . it's a count of employees who completed the modules. I have updated the post. your help is highly appreciated 🙂 

[rbriga]

Just to clarify- why is the expected result = 2?

If it's a count of emplyees who finished their courses, there are 3 (1111,2112,2122);

If it's a count of learnings that all employees (assigned) finished, there's 1 (Microsoft Excel).

What are the 2 you expect to count, in this example?