Forum Discussion

lukexuereb's avatar
lukexuereb
Regular Visitor
5 years ago
Solved

Get Value Associated with the Minimum Number per Group

I have the following table:   Foreign Key Company Name Bid Cheapest Bid 1234 Fruit&Veg Ltd. 12,000 4,000 1234 Plastic Ltd. 4,000 4,000 1234 Technology Plc. 6,000 4,000 56...
Need Help
  • mahoneypat's avatar
    mahoneypat
    5 years ago

    My bad.  Working too fast.  Here you go.

     

    Lowest Bidder =
    VAR cheapest = Bids[Cheapest Bid]
    RETURN
        CALCULATE (
            MIN ( Bids[Company Name] ),
            ALLEXCEPT ( Bids, Bids[Foreign Key] ),
            Bids[Bid] = cheapest
        )

     

    Pat

     

mahoneypat's avatar
mahoneypat
Icon for Microsoft Employee rankMicrosoft Employee
5 years ago

Since you already have a column with cheapest bid, this DAX column expression will get your desired result.  Replace Bids with your actual table name.

 

Lowest Bidder =
CALCULATE (
    MIN ( Bids[Company Name] ),
    ALLEXCEPT ( Bids, Bids[Foreign Key], Bids[Cheapest Bid] )
)

 

Pat

lukexuereb's avatar
lukexuereb
Regular Visitor
5 years ago

Hi mahoneypat 

 

Thanks for the contribution. Unfortunately what this expression does is calculate the company name by taking 'MIN' as 'first in alphabetical order', and disregards the value of the cheapest bid. 

 

Therefore using the above example, Foreign Key 1234 would have displaed a Cheapest bidder of Fruit&Veg Ltd. and 5678 would have displaed a Cheapest bidder of Chewing Gum Plc., since they are both first alphabetically in the list of companies of their respective foreign keys.