Skip to main content
cancel
Showing results for 
Search instead 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.

Reply
RAWPhD
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
Musadev
Resolver II
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])

    )

)

 

View solution in original post

amustafa
Super User
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.

 

amustafa_0-1708999993282.png

 

DAX Measure:

Max Number Measure = MAX(Table1[Number])

 

DAX Calculated summary table

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

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

5 REPLIES 5
amustafa
Super User
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.

 

amustafa_0-1708999993282.png

 

DAX Measure:

Max Number Measure = MAX(Table1[Number])

 

DAX Calculated summary table

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

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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

    )

)

 

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

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 

Helpful resources

Announcements
Microsoft Fabric Learn Together

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

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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