DAX - conditional filters using several column

Question

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&nbsp;number of students in each specialty per level and year.&nbsp;What I haveFor each student, the time ("Day") they passed in a level &nbsp;during the academic year and their specialtyI am trying to "clean up" this database by keeping only the lines for each&nbsp; Student, each year, in his specialty where the number of day is MAX.EXEMPLEAcademic YearSpecialityStudent*LevelDay2023MathA22002023MathA31642022MathA12002022MathA21642023GeoB11622023GeoB22022023GeoC43642023FrenchD51822023FrenchD61822023MathE1102023MathE02502023MathE2104&nbsp;What I want For each students, the level in which they passed the maximum time in the academic year, but we ignore the level 0If there is a tie, the higher level should win&nbsp;RESULTAcademic YearSpecialtyStudentLevelDay2023MathA22002022MathA12002023GeoB22022023GeoC43642023FrenchD61822023MathE2104&nbsp;The goal at the very end,&nbsp; is to be able to extract a table like the one below for each yearnumber of students in each specialty per level and year2023Specialty/Level123456Math&nbsp;2&nbsp;&nbsp;&nbsp;&nbsp;Geo&nbsp;1&nbsp;1&nbsp;&nbsp;French&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1&nbsp;2022Specialty/Level123456Math1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Geo&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;French&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Can someone help me find a solution to this tricky (for me 😉 question?&nbsp;Thanks a gain for your help, I am learning so much each time I am asking something here!&nbsp;Best,&nbsp;Nathalie

amitchandak · Accepted Answer

NathalieM , You need a solution like
&nbsp;
Last Qty = Var _max = maxx(filter( ALLSELECTED(Data1), Data1[Academic Year] = max(Data1[Academic Year]) &amp;&amp; Data1[Speciality] = max(Data1[Speciality]) &amp;&amp; Data1[Student] = max(Student]) ),Data1[Day])return CALCULATE(Max(Data1[Level]), filter( ALLSELECTED(Data1), Data1[Academic Year] = max(Data1[Academic Year]) &amp;&amp; Data1[Speciality] = max(Data1[Speciality]) &amp;&amp; Data1[Student] = max(Student]) &amp;&amp; Data1[Day] =_max ) )
&nbsp;
&nbsp;
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-f1c839ee884e
&nbsp;
&nbsp;
&nbsp;

Forum Discussion

DAX - conditional filters using several column

2 Replies

Related content

Slicer for filtering several columns

Average calculation with several conditions

Conditional Filtering

Grouped summaries with conditional filtering

Filter with conditions

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

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