cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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 III

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 III

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 III

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

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors