Distinct count of ID, but on a maximum of another column

[jenmcnam]

Hi All!

I am currently creating a dashboard regarding student test scores for a placement testing. I am trying to create a bar chart visual that has a distinct count for the student scores. However, the issue I am having is that student's are able to take the placement test more than once, really an unlimited amount of times, so they have multiple entries in my data. I want to create a bar chart that takes their highest score from a certain column, instead of all their scores and/or using the distinct count feature on the axis (since I am not sure which value this is keeping from the student). Is there a way to do this without restructuring my data? Here is an example of the data: Out of this data, I would want the bar chart to take in the data ID/Reading band: ID 1 - Reading Band 221-249, ID 2 - Reading Band 250+, and ID 3 - Reading Band 250+

ID	Reading Band
1	<=220
1	221-249
2	250+
3	221-249
3	<=220
3	250+

[Anonymous · Accepted solution]

Hi @jenmcnam ,

First of all, many thanks to  for your very quick and effective replies.

Based on my testing, please try the following methods:

1.Create the simple table.

2.Create the new measure to filter highest score.

Highest Score = 
CALCULATE(
    MAX('Table'[test scores]),
    ALLEXCEPT('Table', 'Table'[ID])
)

3.Drag the measure into the bar chart.

4.The result is shown below.

Best Regards,

Wisdom Wu

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

[some_bih]

Hi @jenmcnam it should be easy done with MAX over column.