Issue in finding Cumulative status

[Anonymous]

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 · Accepted solution]

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]

Hi @nandukrishnavs 

Thanks for the quick response.

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