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 Status.

For your reference,

      Case1:- If all lessons are completed, then the status is Completed.

      Case2:- If all lessons are NotStarted, then the status is Not Started.

      Case3:- if Case1 and Case2  fails then the status is InProgress.

 

Finally we need to find how many chapters yet to complete who are all in "InProgress" status in a Particular course.

 

 

User

Course

Chapter

Lesson

Lesson Status

1

Physics

P1

1

Not Started

1

Physics

P1

2

Completed

1

Physics

P1

3

In-Progress

1

Physics

P2

1

Completed

1

Physics

P2

2

Completed

2

Physics

P1

1

In-Progress

2

Physics

P1

2

Not Started

2

Physics

P1

3

In-Progress

2

Physics

P2

1

Not Started

2

Physics

P2

2

Not Started

1

Biology

B1

1

Not Started

1

Biology

B1

2

Completed

1

Biology

B1

3

In-Progress

1

Biology

B2

1

Not Started

1

Biology

B2

2

Not Started

2

Biology

B1

1

In-Progress

2

Biology

B1

2

Not Started

2

Biology

B1

3

In-Progress

2

Biology

B2

1

Completed

2

Biology

B2

2

Completed

 

 

  • 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

     

2 Replies

  • 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.