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, s...
  • 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"
    )
     

     

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