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

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

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
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors