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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Darko_Giac
Helper II
Helper II

DAX: Highest Value by Date

Hi all,

I'm currently attempting to work through a DAX formula that will group my highest value and the date that highest value was submitted.

 

My data set consists of Date Submitted, Bid, Room and Employee columns. The basic idea is that employees are bidding on certain rooms to rent out for the day, however, if two employees bid the same amount, I would need a way to distinguish a winner (and I am currently attempting to filter on date submitted, which has a time stamp, so that the employee who bid first would win the room).

I've attached a sample workbook to hopefully clarify the issue.

 

SampleWorkbook

 

Any suggestions would be greatly appreciated!

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hiya.

 

Here's your column formula:

Winning Bidder = 
var __currentRoom = RealTimeData[Room]
var __recordsWithCurrentRoom =
    FILTER(
        RealTimeData,
        RealTimeData[Room] = __currentRoom
    )
var __winnerRecord =
    TOPN(
        1,
        __recordsWithCurrentRoom,
        RealTimeData[bid],
        DESC,
        RealTimeData[DateSubmit],
        ASC
    )
var __winner =
    MAXX(
        __winnerRecord,
        RealTimeData[employee]
    )
return
    __winner

Best

Darek

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

Hi.

 

Is this to be a measure or calculated column? Excel sucks when it comes to authoring DAX formulas - the formula bar is just a pain to use :((( It's much better to send .pbix files in the future, just so you know.

 

Best

Darek

Hey hey, @Anonymous  this would be a calculated column 🙂

 

I'm able to return the earliest date of the two bids, but I would like to only return the name of the person who put in the earliest bid.
 
Here is an updated .pbix workbook.
 
Anonymous
Not applicable

Hiya.

 

Here's your column formula:

Winning Bidder = 
var __currentRoom = RealTimeData[Room]
var __recordsWithCurrentRoom =
    FILTER(
        RealTimeData,
        RealTimeData[Room] = __currentRoom
    )
var __winnerRecord =
    TOPN(
        1,
        __recordsWithCurrentRoom,
        RealTimeData[bid],
        DESC,
        RealTimeData[DateSubmit],
        ASC
    )
var __winner =
    MAXX(
        __winnerRecord,
        RealTimeData[employee]
    )
return
    __winner

Best

Darek

Thanks so much @Anonymous ! Worked like a charm 🙂

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.