Matching records based on time of day criteria

[saberxray]

I have a problem that's been plaguing me for a while.

I have two tables, one table is call records called 'Verint'. These records are created when a user receives a phone call. There is a time stamp recorded. Here's a simplified example of what it looks like:

'VERINT''CALL TICKET DATA'

The table comes from the data when the user makes notes on the call. This is in a different system from the one that records the phone call. It too has a time stamp.

What I would like to do is match the record number from the 'Calls Ticket Data' table to the 'Verint' table. The time stamps will not match, since the time stamp in 'Verint' registers as soon as the user gets a call, but the time stamp in 'Call Ticket Data' does not register until the user creates the ticket. Due to human error, I can never get a perfect match 100% of the time, but I figure I can get close using certain criteria:

- The agent ID has to match

- The date has to match

- The Open Time on the 'Call Ticket Data' table has to be the CLOSEST time to the Call Time in the 'Verint' table without being BEFORE the time on the 'Verint' table.

I've tried modelling it in Excel using VLOOKUP and Index Matching. I've tried using DAX or M, and I can't seem to figure it out. The closest I've gotten is doing a merge between the 'Call Ticket Data' and 'Verint' table on the Agent ID and Call Date, but I can't seem to figure out the logic to filter out the irrelevant rows.

Any help or guidance would be appreciated...I've been working on this on and off for almost a year now.

[v-yuta-msft · Accepted solution]

Hi saberxray,

Based on your condition, please refer to steps below:

1. Click -> Query Editor -> Merge table 'Verint' with table 'Calls Ticket Data' as a new table ‘Merge1’ using left outer join.
2. Create a calculate column [result] using DAX formula:

   result =
   
   VAR min_time = CALCULATE(MIN(Merge1[Call Time.1]), FILTER(Merge1, Merge1[ID] = EARLIER(Merge1[ID]) && Merge1[Call Date] = EARLIER(Merge1[Call Date]) && Merge1[Call Time] = EARLIER(Merge1[Call Time]) && Merge1[Call Time] < Merge1[Call Time.1]))
   
   RETURN
   
   IF(Merge1[Call Time.1] = min_time, Merge1[Call Time.1], BLANK())

   
3. To delete row with blank value, create a measure [Final_result] using DAX formula:

Final_result = CALCULATE(MAX(Merge1[result]), FILTER(Merge1, Merge1[result] <> BLANK()))

     4.  Change time format of [Final_result] with “(h:mm:ss tt)”.

PBIX file here: https://www.dropbox.com/s/5zsre3nylrpwcoi/For%20saberxray.pbix?dl=0

Best Regards,

Jimmy Tao

[saberxray]

Thanks Jimmy, works like a charm. I found another solution after I posted where I created a new merged table, filtered out any times earlier than the acceptable call time, grouped by the earliest time, and remerged, but this is a lot simpler!