How to Display Highest Value in Column 2 for each unique value in Column 1

[Anonymous]

I do not want to delete duplicate rows.  I just want to display the row that has the highest value for the group of duplicates.  For example, I have two columns - one has doc ID, then other has a number.

DOC ID     Number

DOCA_1     6

DOCA_1     5

DOCA_1     2

DOCB_1     7

DOCB_1     9

I want to return:

DOCA_1     6

DOCB_1      9

I want to retain the information for the other rows for other visuals, so I do not want to remove the rows. 

I just want to display the highest value in column 2, for each unique item in column 1. 

Is there a way to do that?

Thanks!

[Musadev · Accepted solution]

You want to create a measure to show highest value for each Doc ID?

 Try the below code to get the desire output.

Highest Value Per DOC_ID = 

MAXX(

    VALUES('YourTable'[DOC_ID]),

    CALCULATE(

        MAX('YourTable'[YourNumberColumn]),

        ALLEXCEPT('YourTable', 'YourTable'[DOC_ID])

    )

)

[amustafa · Accepted solution]

Hi @Anonymous You can do it in 3 ways. You can change Sum to Max on your Number column in your visual or create a measure or create a summary table.

DAX Measure:

Max Number Measure = MAX(Table1[Number])

DAX Calculated summary table

MaxNumberPerDocID = 
SUMMARIZE(
    Table1,
    Table1[DOC_ID],
    "MaxNumber", MAX(Table1[Number])
)

[Anonymous]

Brilliant!  The first solution worked perfectly and easily - thank you so much!!!

I will check the other options later - thanks for giving me options!! 🙂

P.S. Just checked - the other solutions also worked just as easily - thank you so much!!! 

[Musadev · Accepted solution]

You want to create a measure to show highest value for each Doc ID?

 Try the below code to get the desire output.

Highest Value Per DOC_ID = 

MAXX(

    VALUES('YourTable'[DOC_ID]),

    CALCULATE(

        MAX('YourTable'[YourNumberColumn]),

        ALLEXCEPT('YourTable', 'YourTable'[DOC_ID])

    )

)

[Anonymous]

Your solution also worked!! 🙂 Thank you so much - this is an amazing community and just saved me HOURS of work - thank you!!

[Musadev]

You are most welcome. I have joined recently and it is an amazing platform, where there is always someone to help. Good luck for your journey