Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Issue in finding Cumulative status

Hi Team,   I'm facing an issue to find the each status. Already we have a Lesson Status, based on that status we need to find Chapter status.   --> Based on chapter status we need to find Course S...
  • nandukrishnavs's avatar
    nandukrishnavs
    6 years ago

    Hi Anonymous ,

     

    Create a calculated column for Chapter Status

     

    Chapter Status = 
VAR NotStarted =
    CALCULATE (
        COUNTROWS ( 'Learning History' ),
        ALLEXCEPT (
            'Learning History',
            'Learning History'[User],
            'Learning History'[Course],
            'Learning History'[Chapter]
        ),
        'Learning History'[Lesson Status] = "Not Started"
    )
VAR Completed =
    CALCULATE (
        COUNTROWS ( 'Learning History' ),
        ALLEXCEPT (
            'Learning History',
            'Learning History'[User],
            'Learning History'[Course],
            'Learning History'[Chapter]
        ),
        'Learning History'[Lesson Status] = "Completed"
    )
VAR InProgress =
    CALCULATE (
        COUNTROWS ( 'Learning History' ),
        ALLEXCEPT (
            'Learning History',
            'Learning History'[User],
            'Learning History'[Course],
            'Learning History'[Chapter]
        ),
        'Learning History'[Lesson Status] = "In-Progress"
    )
VAR result =
    IF (
        ( InProgress > 0
            && NotStarted > 0 )
            || InProgress > 0,
        "In-Progress",
        IF ( NotStarted > 0, "Not Started", IF ( Completed > 0, "Completed", "" ) )
    )
RETURN
    result

     

    Create a calculated column for Course Status

     

    Course Status = 
VAR NotStarted =
    CALCULATE (
        COUNTROWS ( 'Learning History' ),
        ALLEXCEPT (
            'Learning History',
            'Learning History'[User],
            'Learning History'[Course]            
        ),
        'Learning History'[Chapter Status] = "Not Started"
    )
VAR Completed =
    CALCULATE (
        COUNTROWS ( 'Learning History' ),
        ALLEXCEPT (
            'Learning History',
            'Learning History'[User],
            'Learning History'[Course]           
        ),
        'Learning History'[Chapter Status] = "Completed"
    )
VAR InProgress =
    CALCULATE (
        COUNTROWS ( 'Learning History' ),
        ALLEXCEPT (
            'Learning History',
            'Learning History'[User],
            'Learning History'[Course]
        ),
        'Learning History'[Chapter Status] = "In-Progress"
    )
VAR result =
    IF (
        ( InProgress > 0
            && NotStarted > 0 )
            || InProgress > 0,
        "In-Progress",
        IF ( NotStarted > 0, "Not Started", IF ( Completed > 0, "Completed", "" ) )
    )
RETURN
    result

     

    Now you can simply find, how many chapters yet to complete who are all in "InProgress" status in a particular course.

     

    Regards,

    Nandu Krishna

     

nandukrishnavs's avatar
nandukrishnavs
Icon for Community Champion rankCommunity Champion
6 years ago

Hi Anonymous ,

 

Create a calculated column for Chapter Status

 

Chapter Status = 
VAR NotStarted =
    CALCULATE (
        COUNTROWS ( 'Learning History' ),
        ALLEXCEPT (
            'Learning History',
            'Learning History'[User],
            'Learning History'[Course],
            'Learning History'[Chapter]
        ),
        'Learning History'[Lesson Status] = "Not Started"
    )
VAR Completed =
    CALCULATE (
        COUNTROWS ( 'Learning History' ),
        ALLEXCEPT (
            'Learning History',
            'Learning History'[User],
            'Learning History'[Course],
            'Learning History'[Chapter]
        ),
        'Learning History'[Lesson Status] = "Completed"
    )
VAR InProgress =
    CALCULATE (
        COUNTROWS ( 'Learning History' ),
        ALLEXCEPT (
            'Learning History',
            'Learning History'[User],
            'Learning History'[Course],
            'Learning History'[Chapter]
        ),
        'Learning History'[Lesson Status] = "In-Progress"
    )
VAR result =
    IF (
        ( InProgress > 0
            && NotStarted > 0 )
            || InProgress > 0,
        "In-Progress",
        IF ( NotStarted > 0, "Not Started", IF ( Completed > 0, "Completed", "" ) )
    )
RETURN
    result

 

Create a calculated column for Course Status

 

Course Status = 
VAR NotStarted =
    CALCULATE (
        COUNTROWS ( 'Learning History' ),
        ALLEXCEPT (
            'Learning History',
            'Learning History'[User],
            'Learning History'[Course]            
        ),
        'Learning History'[Chapter Status] = "Not Started"
    )
VAR Completed =
    CALCULATE (
        COUNTROWS ( 'Learning History' ),
        ALLEXCEPT (
            'Learning History',
            'Learning History'[User],
            'Learning History'[Course]           
        ),
        'Learning History'[Chapter Status] = "Completed"
    )
VAR InProgress =
    CALCULATE (
        COUNTROWS ( 'Learning History' ),
        ALLEXCEPT (
            'Learning History',
            'Learning History'[User],
            'Learning History'[Course]
        ),
        'Learning History'[Chapter Status] = "In-Progress"
    )
VAR result =
    IF (
        ( InProgress > 0
            && NotStarted > 0 )
            || InProgress > 0,
        "In-Progress",
        IF ( NotStarted > 0, "Not Started", IF ( Completed > 0, "Completed", "" ) )
    )
RETURN
    result

 

Now you can simply find, how many chapters yet to complete who are all in "InProgress" status in a particular course.

 

Regards,

Nandu Krishna

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi nandukrishnavs 

     

    Thanks for the quick response.

     

    Your dax query is working in  my case and getting the correct result.