Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
43 | |
23 | |
21 | |
15 | |
15 |
User | Count |
---|---|
45 | |
31 | |
30 | |
18 | |
17 |