Get the subject in which students scored the max marks using DAX Power BI

[Anonymous]

Consider the Table below

ID	Subject	Marks
1	Maths	75
1	Science	68
1	English	95
1	Hindi	83
1	SST	72
2	Maths	98
2	Science	72
2	English	90
2	Hindi	68
2	SST	70

Now i Need to find the subject in which student has scored max marks. Please help with DAX to achieve this
Expected Output

ID	Max Marks	Subject
1	95	English
2	98	Maths

[Anonymous]

Thanks John what if i just need to get the subject in which student scored the max marks via measure

[johnt75]

You can do this with a couple of measures

Top Subject = CONCATENATEX( TOPN( 1, 'Table',  'Table'[Marks]), 'Table'[Subject], ", ")

Top Marks = MAX('Table'[Marks])

In case of ties this will return a comma separated list of subjects which shared the same top marks.

[Anonymous]

Thanks John what if i just need to get the subject in which student scored the max marks via measure

[v-xiaotang · Accepted solution]

Hi @Anonymous 

You can try this,

create a measure

maxmarks = CALCULATE( MAX('Table'[Marks]),ALLEXCEPT('Table','Table'[ID]))

then create the table below,

output table = FILTER('Table','Table'[Marks]=[maxmarks])

result

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.