Count Users with Multiple Completion as 1

[imdavidlee]

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:

[bhanu_gautam · Accepted solution]

@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"
)