Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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 )
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.
Hello Jihwan_JHKIM,
It works well. Thanks for your help!
Cheers,
Tracy
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 )
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
25 | |
20 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |