topic Count Users with Multiple Completion as 1 in DAX Commands and Tips

Count Users with Multiple Completion as 1

imdavidlee — Wed, 31 Jul 2024 05:24:35 GMT

Hello,

I have a scenario like this - each user has to attend multiple different training to complete the curriculum. I have same user ID appeared multiple times for each training assigned to them, similarly to the completion Status. How can we distinct count the "Complete" Status as 1 for user who completed all training?

Here's the data:

Here is the output count I want to achieve:

Re: Count Users with Multiple Completion as 1

bhanu_gautam — Wed, 31 Jul 2024 07:02:44 GMT

imdavidlee , First Create a calculated column

User Status =

IF(

CALCULATE(

COUNTROWS('Table'),

'Table'[Status] = "Incomplete",

ALLEXCEPT('Table', 'Table'[User])

) > 0,

"Incomplete",

"Complete"

)

Then create a new summary table , by clicking new table in modelling

SummaryTable =

SUMMARIZE(

'Table',

'Table'[User],

"Status", MAX('Table'[User Status])

)

Then create 2 measure for count

Count of Completion =
CALCULATE(
COUNTROWS(SummaryTable),
SummaryTable[Status] = "Complete"
)

Count of Incompletion =
CALCULATE(
COUNTROWS(SummaryTable),
SummaryTable[Status] = "Incomplete"
)