Return text value based on Max of another column and take earliest instance if multiple Max occurs

[loafers]

Hi,

I want get the Location for the max sales for each unique ID in table below. 

I have the following measure:

Measure = CALCULATE(VALUES('Table'[Location]),'Table','Table'[Sales]=MAX('Table'[Sales]))

The measure works if the max of the sales field only occurs once. However, if the max of sales for the Unique ID shows up multiple times, I run into an error. Is there a way to have it pick the earliest instance of the max? So in the table below, for unique ID 1, I would want to return 'A'

Location	UniqueID	Sales
A	1	50
B	1	50
C	1	40
D	2	30

[Anonymous · Accepted solution]

Hi @loafers ,

I have created a simple sample, please refer to it to see if it helps you.

Create a measure.

Measure = CALCULATE(SUM('Table'[Sales]),FILTER(ALL('Table'),'Table'[UniqueID]=SELECTEDVALUE('Table'[UniqueID])))

Then click the location field.

If I have misunderstood your menaing, please provide your pbix file without privacy information and desired output with more details.

Best Regards
Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@loafers , Create a rank with tie break and use that visual level filter

Rank Tie breaker
https://community.powerbi.com/t5/Community-Blog/Breaking-Ties-in-Rankings-with-RANKX-Using-Multiple-...
https://databear.com/how-to-use-the-dax-rankx-function-in-power-bi/

Or Try TOPN : https://youtu.be/QIVEFp-QiOk

[loafers]

I have to dissect the rankx link a bit further but it looks like the values used are unique? 

My end result that I want from the original table posted is something like below where sales is total sales for the Location and UniqueID.

Location	UniqueID	Sales
A	1	140
D	2	30