Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
So I need to show student journey - find the number of student completed all the course in each month.
There are 2 tables.
1) Courses details(Course master) Columns(ID, CourseName) 1-CourseA, 2-CourseB ...5-CourseE. 5 courses total.
2) Transaction Table. Having details StudentId, CourseID, CompletedDate(DateTimeStamp) etc.
Lets say Student1 completed 2 courses(A and B) in May, 1 course(D) in June, 1 course(C) in July and 1 course(E) in Aug. Student Journey is completed in August.
Total student are 500, some will complete all 5 course in May, some in June etc.
Sample Data Screenshot for expected result
Any help is appreciated
Solved! Go to Solution.
Hi @nik1
I have built my measure on top of @Jihwan_Kim's sample file. However, as per my understanding of your requirement I followed a completely different approach. https://www.dropbox.com/t/AMTGGmXFHZTBDa79
Count =
VAR NumberOfCourses = COUNTROWS ( ALLSELECTED ( Course[CourseName] ) )
VAR CurrentDate = MAX ('Date'[Date] )
VAR Completed =
FILTER (
ALL ( Data[StudentID] ),
VAR TableOnAndBefore = CALCULATETABLE ( Data,'Date'[Date] <= CurrentDate )
VAR LastCompletedDate = MAXX ( TableOnAndBefore, Data[CompletedDate] )
RETURN
COUNTROWS ( TableOnAndBefore ) = NumberOfCourses
&& MONTH ( CurrentDate ) = MONTH ( LastCompletedDate )
)
RETURN
COUNTROWS ( Completed )
Hi @nik1
Here is the updated file as requested https://www.dropbox.com/t/3IXKtCQpmt9MwReQ
Count =
VAR CurrentMonth = MAX ( 'Date'[Year Month Number] )
VAR NumberOfCourses = COUNTROWS ( ALLSELECTED ( Course[CourseName] ) )
VAR CurrentDate = MAX ( 'Date'[Date] )
VAR T1 = FILTER ( All ( Data ), Data[CompletedDate] <= CurrentDate )
RETURN
SUMX (
ALL ( Data[StudentID] ),
VAR Student = Data[StudentID]
VAR T2 = FILTER ( T1, Data[StudentID] = Student )
RETURN
IF ( COUNTROWS ( T2 ) = NumberOfCourses, 1, 0 )
)
Hi @nik1
I have built my measure on top of @Jihwan_Kim's sample file. However, as per my understanding of your requirement I followed a completely different approach. https://www.dropbox.com/t/AMTGGmXFHZTBDa79
Count =
VAR NumberOfCourses = COUNTROWS ( ALLSELECTED ( Course[CourseName] ) )
VAR CurrentDate = MAX ('Date'[Date] )
VAR Completed =
FILTER (
ALL ( Data[StudentID] ),
VAR TableOnAndBefore = CALCULATETABLE ( Data,'Date'[Date] <= CurrentDate )
VAR LastCompletedDate = MAXX ( TableOnAndBefore, Data[CompletedDate] )
RETURN
COUNTROWS ( TableOnAndBefore ) = NumberOfCourses
&& MONTH ( CurrentDate ) = MONTH ( LastCompletedDate )
)
RETURN
COUNTROWS ( Completed )
Thanks you @tamerj1 the solution is working good.
There is little more tweek required if you can help. We also need to show Cummulative total/Running total for each month.
Like in the solution file June 2022 has 1 count, July 2022 has 0 count but R.Total for July 2022 should be 1.
Example -> Current Month Count + previous month total
Hi @nik1
Here is the updated file as requested https://www.dropbox.com/t/3IXKtCQpmt9MwReQ
Count =
VAR CurrentMonth = MAX ( 'Date'[Year Month Number] )
VAR NumberOfCourses = COUNTROWS ( ALLSELECTED ( Course[CourseName] ) )
VAR CurrentDate = MAX ( 'Date'[Date] )
VAR T1 = FILTER ( All ( Data ), Data[CompletedDate] <= CurrentDate )
RETURN
SUMX (
ALL ( Data[StudentID] ),
VAR Student = Data[StudentID]
VAR T2 = FILTER ( T1, Data[StudentID] = Student )
RETURN
IF ( COUNTROWS ( T2 ) = NumberOfCourses, 1, 0 )
)
Hi,
Please check the below picture and the attached pbix file.
I tried to create a sample pbix file like below. I hope the below can provide some ideas on how to create a solution for your dataset.
All courses finish month: =
FORMAT (
MAXX (
GROUPBY (
Data,
Data[CompletedDate],
"@lastcompleteddate", MAXX ( CURRENTGROUP (), Data[CompletedDate] )
),
[@lastcompleteddate]
),
"MMMM-YYYY"
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Thanks @Jihwan_Kim for replying and taking time to help here.
Update - I tried this solution as well, but results no as expectedDid not try you solution since it is not what the expected results are.
I have update the post with sample data screenshot and expected results, if that can give more clarity.
Apprecaite your help
@nik1 , Assume total courses are 5
then
countx(filter( summarize(Table, Table[Student ID], "_1", calculate(distintcount(Table[CourseName])) ) , [_1] =5), [Student ID])
or
calculate(countx(filter( summarize(Table, Table[Student ID], "_1", calculate(distintcount(Table[CourseName])) ) , [_1] =5), [Student ID]), filter(Table, Table[Date] <= selectedvalue(Date[Date]) )
assuming the date is an independent date table
Thanks @amitchandak for replying and taking time to help here.
tried your suggestion but measure did not retun any results.
I have update the post with sample data screenshot and expected results, if that can give more clarity.
Apprecaite your help