How to determine Time Occurred for Max Wait

[LEM8720]

Hi All,

I am trying to work out how to show the time that a maximum wait occured for each queue group in our call centre.

Below is a screenshot of the data that I am working with. I have the Maximum Wait for each of the Queues, but I want to know what time that occured at.  I have another sheet which has each time period and that wait time for each skill but I am having trouble gettting the time occured to appear.

I have tried using VLookup but I keep getting an error stating that multiple values were returned. 

Would anyone have any insights into how to get this to work?

Thanks!

[mussaenda]

Hi @LEM8720 , 

Please show your desired output. 

I can see on your second photo there two values for Registration Intro at 8.15 am

Thanks

[LEM8720]

I am looking to try and get a table like this ;

I want to know the time that the Max Wait occured at. I was able to do the above table in Excel but I haven't been successful yet to replicate this in Power Bi

[amitchandak · Accepted solution]

@LEM8720 , You can create a column Rank or measure rank  example

if Needed use use datetime combine field

Datetime  = [Date]+[time]

Rank column = rankx(filter(Table, [Queue] = earlier([Queue])),[Wait Time],,desc, dense)

Rank measure = rankx(filter(allselected(Table, Table[Queue],Table [Datetime]) ,Table[Queue] = max([Queue])),calculate(sum([Wait Time])),,desc, dense)

filter for Rank 1

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns

[LEM8720]

Thanks @amitchandak That worked.