Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

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

    )

)

 

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

    )

)

 

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Fabric Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.