Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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,
Solved! Go to 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)
)
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.
Try:
SUMX(
VALUES( Tablename[EMPLOYEE] ),
IF(
DISTINCTCOUNT( Tablename[LEARNING] ) =
CALCULATE(
DISTINCTCOUNT( Tablename[LEARNING] ),
KEEPFILTERS( Tablename[STATUS] = "Complete" )
),
1,
BLANK()
)
)
Hi @rbriga
I have tried this. . It seems like it's not working; it's showing a blank result. Any other suggestions . .
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)
)
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 🙂
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?
User | Count |
---|---|
20 | |
18 | |
17 | |
11 | |
7 |
User | Count |
---|---|
28 | |
28 | |
13 | |
12 | |
12 |