topic Re: Calculate Student Journey in DAX Commands and Tips

Calculate Student Journey

nik1 — Wed, 03 Aug 2022 17:41:11 GMT

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

Re: Calculate Student Journey

amitchandak — Wed, 03 Aug 2022 03:41:27 GMT

nik1 , Assume total courses are 5

then

countx(filter( summarize(Table, Table[Student ID], "_1", calculate(distintcount(Table[CourseName])) ) , [_1] =5), [Student ID])

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

Re: Calculate Student Journey

Jihwan_Kim — Wed, 03 Aug 2022 05:21:01 GMT

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" )

Re: Calculate Student Journey

nik1 — Wed, 03 Aug 2022 17:43:22 GMT

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

Re: Calculate Student Journey

nik1 — Mon, 08 Aug 2022 16:45:31 GMT

Thanks Jihwan_Kim for replying and taking time to help here.
Update - I tried this solution as well, but results no as expected
~~Did 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

Re: Calculate Student Journey

tamerj1 — Fri, 05 Aug 2022 21:10:38 GMT

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 )

Re: Calculate Student Journey

nik1 — Mon, 08 Aug 2022 20:42:31 GMT

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

Re: Calculate Student Journey

tamerj1 — Wed, 10 Aug 2022 05:18:55 GMT

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 ) )