Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 |
Solved! Go to 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.
Thanks John what if i just need to get the subject in which student scored the max marks via measure
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.
Thanks John what if i just need to get the subject in which student scored the max marks via measure
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.
User | Count |
---|---|
41 | |
27 | |
23 | |
18 | |
15 |
User | Count |
---|---|
54 | |
35 | |
19 | |
18 | |
15 |