Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
37 | |
25 | |
19 | |
14 | |
8 |
User | Count |
---|---|
69 | |
48 | |
44 | |
19 | |
15 |