cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Regular Visitor

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

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!

2 ACCEPTED SOLUTIONS
Resolver II

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])

)

)

Super User

Hi @RAWPhD 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])
)``````

Proud to be a Super User!

5 REPLIES 5
Super User

Hi @RAWPhD 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])
)``````

Proud to be a Super User!

Regular Visitor

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!!!

Resolver II

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])

)

)

Regular Visitor

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

Resolver II

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

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors