Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

 

 

1 ACCEPTED SOLUTION
nandukrishnavs
Super User
Super User

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

 


Regards,
Nandu Krishna

View solution in original post

2 REPLIES 2
nandukrishnavs
Super User
Super User

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

 


Regards,
Nandu Krishna

Anonymous
Not applicable

Hi @nandukrishnavs 

 

Thanks for the quick response.

 

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

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors