The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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 |
Solved! Go to 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
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
Thanks for the quick response.
Your dax query is working in my case and getting the correct result.