Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
User | Count |
---|---|
41 | |
27 | |
23 | |
19 | |
16 |
User | Count |
---|---|
55 | |
35 | |
21 | |
18 | |
15 |