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

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

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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