cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

## 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

1 ACCEPTED SOLUTION
Impactful Individual

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)

)

-------------------------
Data analyst by day, hockey goalie by night.
Did I help? Then please hit that "kudos" or "accept as a solution" button!
7 REPLIES 7
Super User

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.

Impactful Individual

Try:

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

-------------------------
Data analyst by day, hockey goalie by night.
Did I help? Then please hit that "kudos" or "accept as a solution" button!
Frequent Visitor

Hi @rbriga

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

Impactful Individual

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)

)

-------------------------
Data analyst by day, hockey goalie by night.
Did I help? Then please hit that "kudos" or "accept as a solution" button!
Frequent Visitor

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

Frequent Visitor

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 🙂

Impactful Individual

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?

-------------------------
Data analyst by day, hockey goalie by night.
Did I help? Then please hit that "kudos" or "accept as a solution" button!

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors