Forum Discussion

imdavidlee's avatar
imdavidlee
New Member
2 years ago
Solved

Count Users with Multiple Completion as 1

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's avatar
    bhanu_gautam
    2 years ago

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

     

1 Reply

  • bhanu_gautam's avatar
    bhanu_gautam
    Icon for Super User rankSuper User
    2 years ago

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