topic Re: Count based on condition - Completion Status in DAX Commands and Tips

Count based on condition - Completion Status

Juma1231 — Sun, 19 Dec 2021 10:38:45 GMT

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

Thanks in advance,

Re: Count based on condition - Completion Status

rbriga — Sun, 19 Dec 2021 10:19:44 GMT

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?

Re: Count based on condition - Completion Status

Juma1231 — Sun, 19 Dec 2021 10:24:29 GMT

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 🙂

Re: Count based on condition - Completion Status

rbriga — Sun, 19 Dec 2021 11:10:54 GMT

Try:

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

Re: Count based on condition - Completion Status

Juma1231 — Sun, 19 Dec 2021 11:25:21 GMT

Hi rbriga

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

Re: Count based on condition - Completion Status

rbriga — Sun, 19 Dec 2021 11:52:04 GMT

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)

)

Re: Count based on condition - Completion Status

Juma1231 — Sun, 19 Dec 2021 18:47:29 GMT

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

Re: Count based on condition - Completion Status

AlexisOlson — Sun, 19 Dec 2021 20:16:44 GMT

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.