Advanced DAX - Group by to get latest record of each person and then categorize the distribution

[tracyng0905]

Hi folks,

I have a sample data as shown below. Each student has it CGPA Range per Year, Sem, and Prog. 

The objective is to group the result by CGPA Range per each students' latest result per year. for example,

[Case A] if there is no filter on semester,  SID_002 will count into GPA 2.00-2.29 for Year=2016  (Latest Semester =3)

[Case B] if there is filter on Semester=(1 or 2), the SID_002 will count into GPA<2.00 for Year=2016  (Latest Semester =2).

The result is expected as below. 

Student CGPA distribution by latest CGPA:

  

However, i tried many DAX  but none can return correct result. All DAX looks like just counting student by year, sem, prog, CGPA Range instead of everyone's latest result 

Here is tth PBIX Example i am working on. Can someone help me?

Thank you very much for your help!!

Tracy

[tracyng0905]

Hello Jihwan_JHKIM,

It works well. Thanks for your help!
Cheers,

Tracy

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure if I understood your question correctly, but please check the below measure and the attached pbix file.

Count V2: =
VAR currentprogram =
MAX ( Data[Programme Code] )
VAR currentcgpa =
MAX ( Cat[CGPA] )
VAR currentyear =
MAX ( Data[Academic Year] )
VAR newtable =
GROUPBY (
ALLSELECTED ( Data ),
Data[Student Number],
Data[Academic Year],
"@latest", MAXX ( CURRENTGROUP (), Data[YearSem] )
)
VAR filterbynewtable =
CALCULATETABLE (
ALLSELECTED ( Data ),
TREATAS ( newtable, Data[Student Number], Data[Academic Year], Data[YearSem] )
)
VAR currentcontext =
FILTER (
filterbynewtable,
Data[Programme Code] = currentprogram
&& Data[CGPA] = currentcgpa
&& Data[Academic Year] = currentyear
)
RETURN
COUNTROWS ( currentcontext )