topic Re: Advanced DAX - Group by to get latest record of each person and then categorize the distribution in DAX Commands and Tips

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

tracyng0905 — Wed, 12 Jan 2022 19:08:38 GMT

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

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

Jihwan_Kim — Thu, 13 Jan 2022 03:42:50 GMT

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 )

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

tracyng0905 — Fri, 14 Jan 2022 02:08:58 GMT

Hello Jihwan_JHKIM,

It works well. Thanks for your help!
Cheers,

Tracy