Forum Discussion
Count based on condition - Completion Status
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,
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)
)
7 Replies
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.
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?
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 🙂
Try:
SUMX(
VALUES( Tablename[EMPLOYEE] ),
IF(
DISTINCTCOUNT( Tablename[LEARNING] ) =
CALCULATE(
DISTINCTCOUNT( Tablename[LEARNING] ),
KEEPFILTERS( Tablename[STATUS] = "Complete" )
),
1,
BLANK()
)
)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)
)
