Create a new measure based upon the max result of another created measure

[AW1976NOVA]

Hi ,

I'm trying to create a new measure that will return the correct [ICD10 Mapping] based upon the [Member ID] and the Max [Plan Payment Amount] of each [Member ID].

It's important to note that each [Member ID] can and often does populate numerous times in the excel table 'Claim Data'.  Meaning, one [Member ID] could populate 2, 3, 100 or however many times with different [Plan Payment Amount]s.

Here is a table visual showing what I already have built:

I've already gone ahead and created a measure that returns the Highest [Plan Payment Amount] for each [Member ID]:

Max Plan Payment Amount = CALCULATE(MAX('Claim Data'[Plan Payment Amount]),ALLEXCEPT('Claim Data','Claim Data'[Member ID]))

I'd now like to create a new measure that returns the correct [ICD10 Mapping] result for each [Member ID]...with the Highest [Plan Payment Amount].

Here is a dummy dataset for an example:

Member ID     Plan Payment Amount     ICD10 Mapping 

0001               $40.00                              77PR

0001               $55.00                              42KN

0002               $30.00                              ZZBV1

0001               $100.00                            5R3S

0002               $20.00                              CC7T

0044               $450.00                            WQ56

The new measure should return the following:

Member ID     Plan Payment Amount     ICD10 Mapping

0001              $100.00                             5R3S

0002              $30.00                              ZZBV1

0044              $450.00                            WQ56

[Greg_Deckler · Accepted solution]

@AW1976NOVA - I think you want Lookup Min/Max. https://community.powerbi.com/t5/Quick-Measures-Gallery/Lookup-Min-Max/m-p/985814#M434

[AW1976NOVA]

Awesome,

Thank you so much