cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric 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!