DAX - conditional filters using several column

[NathalieM]

Dear community!

I am always amazed by the support we can find there. Please let me first thank you all for your dedication in helping the beginners like me!

My goal is to be able to extract a table where i have the number of students in each specialty per level and year.

What I have

For each student, the time ("Day") they passed in a level  during the academic year and their specialty

I am trying to "clean up" this database by keeping only the lines for each  Student, each year, in his specialty where the number of day is MAX.

EXEMPLE

Academic Year	Speciality	Student*	Level	Day
2023	Math	A	2	200
2023	Math	A	3	164
2022	Math	A	1	200
2022	Math	A	2	164
2023	Geo	B	1	162
2023	Geo	B	2	202
2023	Geo	C	4	364
2023	French	D	5	182
2023	French	D	6	182
2023	Math	E	1	10
2023	Math	E	0	250
2023	Math	E	2	104

What I want

For each students, the level in which they passed the maximum time in the academic year, but we ignore the level 0

If there is a tie, the higher level should win

RESULT

Academic Year	Specialty	Student	Level	Day
2023	Math	A	2	200
2022	Math	A	1	200
2023	Geo	B	2	202
2023	Geo	C	4	364
2023	French	D	6	182
2023	Math	E	2	104

The goal at the very end,  is to be able to extract a table like the one below for each year

number of students in each specialty per level and year

2023

Specialty/Level	1	2	3	4	5	6
Math		2
Geo		1		1
French						1

2022

Specialty/Level	1	2	3	4	5	6
Math	1
Geo
French

Can someone help me find a solution to this tricky (for me 😉 question?

Thanks a gain for your help, I am learning so much each time I am asking something here!

Best, 

Nathalie

[NathalieM]

Thanks a lot Amitchandak.

I have to admit I am not sure I understand fully the formula (why we use max into the filters to allows for a MAXX specific for the student year and specialty), but this worked really nice! I will keep it in mind and use it more in my analysis!  Have a great day!

[amitchandak · Accepted solution]

@NathalieM , You need a solution like

Last Qty = Var _max = maxx(filter( ALLSELECTED(Data1), Data1[Academic Year] = max(Data1[Academic Year]) && Data1[Speciality] = max(Data1[Speciality]) && Data1[Student] = max(Student]) ),Data1[Day])
return
CALCULATE(Max(Data1[Level]), filter( ALLSELECTED(Data1), Data1[Academic Year] = max(Data1[Academic Year]) && Data1[Speciality] = max(Data1[Speciality]) && Data1[Student] = max(Student]) && Data1[Day] =_max ) )

Refer Latest
https://amitchandak.medium.com/power-bi-get-the-last-latest-value-of-a-category-d0cf2fcf92d0

https://amitchandak.medium.com/power-bi-get-the-sum-of-the-last-latest-value-of-a-category-f1c839ee8...