Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
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.
Solved! Go to Solution.
Hi saberxray,
Based on your condition, please refer to steps below:
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())
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
Hi saberxray,
Based on your condition, please refer to steps below:
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())
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
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
98 | |
78 | |
64 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |