Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
PatChan
Frequent Visitor

Time travelling! How to calculate student grade on the fly?

I have the following school data, but I want to plot a line chart of say, no. of grade 3 students in each academic year. I am not able to do that since I only have the current grade for this academic year and when I pass this current grade in the legend, I get the 'journey' of the current grade 3 students in each academic year instead of the true grade 3 in each academic year. In other words, I need to calculate the grades of these students on the fly. The desired outcome is a DAX measure that I can pass to Y axis to plot my line chart (no. of grade X against academic year). So I came up with a DAX for grade 12 (will replicate for other grades), but it does not seem to achieve what I need. There is, however, no error in the DAX.

My logic for the DAX is that provided the current academic year is 2022 since it is before 1 August 2023, and student with school ID 1234 is in grade 3. When the academic year is 2021, this student would be in grade 2, and in 2020 grade 1, and in 2019 grade 0, but because thist student joined in grade 1, so I want the result to be null.
 

School IDJoining GradeCurrent Grade
123413
567823
9101 4
121346

 

Dynamic year 12 =
VAR CurrentYear = YEAR(TODAY())
VAR CurrentMonth = MONTH(TODAY())
VAR CurrentAcademicYear = IF(CurrentMonth < 8, CurrentYear - 1, CurrentYear)
VAR ChosenYear = SELECTEDVALUE('date_table'[Year])
VAR YearDifference = CurrentAcademicYear - ChosenYear
VAR CurrentGrade = MAXX(SUMMARIZE('student_table','student_table'[SchoolID],'student_table'[current_grade]), [current_grade])
VAR EnrolmentGrade = MAXX(SUMMARIZE('student_table','student_table'[SchoolID],'student_table'[joining_grade]), [joining_grade])
VAR Result =
    IF(
        NOT ISBLANK(EnrolmentGrade),
        IF(
            CurrentGrade - YearDifference = 12,
            1,
            IF(
                CurrentGrade - YearDifference < EnrolmentGrade,
                0,
                CurrentGrade - YearDifference
            )
        )
    )
RETURN
    Result

Thank you for your help in advance and appreciate your time in helping me 🙂
5 REPLIES 5
v-rzhou-msft
Community Support
Community Support

Hi @PatChan ,

 

I suggest you to create two unrelated tables DimYear and DimGrade to help calculation.

Line Point = 
VAR _1 =
    GENERATE ( GENERATE ( ALL(DimYear), ALL(DimGrade) ), CALCULATETABLE(VALUES ( 'Table'[School ID] ),ALL('Table') ))
VAR _2 =
    FILTER (
        _1,
        VAR _START =
            CALCULATE (
                SUM ( 'Table'[Joining Grade] ),
                FILTER ( 'Table', 'Table'[School ID] = EARLIER ( [School ID] ) )
            )
        VAR _END =
            CALCULATE (
                SUM ( 'Table'[Current Grade] ),
                FILTER ( 'Table', 'Table'[School ID] = EARLIER ( [School ID] ) )
            )
        RETURN
            [Grade] >= _START
                && [Grade] <= _END
    )
VAR _3 =
    ADDCOLUMNS (
        _2,
        "Correct Year",
            YEAR ( TODAY () )
                - RANKX (
                    FILTER ( _2, [School ID] = EARLIER ( [School ID] ) ),
                    [Grade],
                    ,
                    DESC,
                    DENSE
                ) + 1
    )
VAR _4 =
    FILTER ( _3, [Year] = [Correct Year] )
RETURN
SUMX(FILTER(_4,[Grade] = MAX(DimGrade[Grade]) && [School ID] = MAX('Table'[School ID])),[Correct Year])

Result is as below.

vrzhoumsft_0-1686037375225.png

 

Best Regards,
Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-rzhou-msft Thank you for evening sending me the file. This is a very interesting way to approach the problem. However, I foresee this approach will be problematic if I have 10k+ students because there will be too many lines. Ideally, the Y axis should be the count of student and X axis is the year. This, accompanied by a grade filter so that when I filter for grade 12, the line chart will show the number of true grade 12 students as of different year.

Quick reminder why we have this problem is because the existing grade column in the dataset is the current grade. So when we filter grade 12 using this column, it will show the correct number of grade 12 this year (AY2022), but for the earlier years (e.g., AY2021) they will all be wrong because the AY2021 number should instead show the number of grade 12 in AY2021 (which are now grade 13 in AY2022), but this group of students will not be shown because of the existing filter of grade 12. Hope this clarifies. 

Update: Luke_0 has kindly come up with a solution that solved the problem!
dynamic - Time travelling! How to calculate student grade on the fly? - Stack Overflow

Hi @PatChan ,

 

Could you share a sample file with me and show me a screenshot with me? This will make it easier for me to find the solution. If your data size is large, please optmize your data model to get better performance.

 

Best Regards,
Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

some_bih
Super User
Super User

Hi @PatChan "thinking in DAX" is not easy 🙂 As I understand your grade values depend on two criterias: date when student join and another possible criteria to check if what date is current academic year (the current academic year is 2022 since it is before 1 August 2023)? Each student ID got date of joining?

provide some sample data and expected output with examples.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors