Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The 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.

Reply
Juma1231
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 IDLEARNINGLEARNING STATUSMODULE
3421Microsoft ExcelComplete Microsoft Office 
3421Microsoft WordIncompleteMicrosoft Office 
1111Microsoft ExcelComplete Microsoft Office 
2112Microsoft ExcelComplete Microsoft Office 
2122Microsoft WordComplete Microsoft Office 
3222Business LanguageIncompleteCommunication Skills

 

 

Target value ( Modules completed): 

 

Microsoft Office = 3 

 

Thanks in advance,

1 ACCEPTED SOLUTION
rbriga
Impactful Individual
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!

View solution in original post

7 REPLIES 7
AlexisOlson
Super User
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.

 

rbriga
Impactful Individual
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!

Hi @rbriga 

 

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

rbriga
Impactful Individual
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!

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

Juma1231
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 🙂 

rbriga
Impactful Individual
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!

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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