Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Return another column based on condition

I am looking to return highest value  for city and corresponding vendor in my formula 

City  Vendor  Price

A         1           30

A         2           50

A         3          100

B         1            35

B          2           40

C         1            60

 

Measure

 

Highest Price = CALCULATE(MAX(VendorRate[Price]),ALLEXCEPT(VendorRate[City]) Its populating highest price but not the vendor. I am using Top N in the visual filter but its displaying incorrectly Thanks you in Advance

Need Help
  • v-chuncz-msft's avatar
    v-chuncz-msft
    7 years ago

    Anonymous 

     

    You may use the measure below.

    Measure =
MAXX ( TOPN ( 1, VendorRate, VendorRate[Price], DESC ), VendorRate[Vendor] )

3 Replies

  • v-chuncz-msft's avatar
    v-chuncz-msft
    Community Support
    7 years ago

    Anonymous 

     

    You may use the measure below.

    Measure =
MAXX ( TOPN ( 1, VendorRate, VendorRate[Price], DESC ), VendorRate[Vendor] )
  • parry2k's avatar
    parry2k
    Super User
    7 years ago

    Anonymous add a unique identifier like index column using power query and then use your measure to return index. once you have index, you can get whatever column you need for that row price/vendor etc.

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    You can use Rank funtion in mesure to calculate rank based on condition then just filter where rank is 1 or create a new column in the table with Rank function and then apply filter where it is 1.