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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Aymeric_2350
Frequent Visitor

how to match partial text from two tables and provide a datediff ?

Hi all,

 

I have two tables where all emails received/sent are recorded with the following columns/information

Aymeric_2350_0-1670307595973.png

What I'd like to do is calculate the time between the email being received and the reply being sent for each email separately in order to see based on each time increment (either yearly, monthly or weekly) how many emails were answered within specific timeframes ...

Can anyone assist with how you could go about to "match" each specific email to its particular answer (there should be only the addition of the prefix "RE:" to the email subject ?

Then, would datediff work to calculate the timeframe elapsed between them?

2 REPLIES 2
Aymeric_2350
Frequent Visitor

Sorry, I don't see how this apply here ... so I'll try to explain differently (I hope it's clarifies things a bit)

 

So what I'm aiming to do is have a search in one excel sheet for the title of email (received) and search in another excel sheet the corresponding email (sent) then get a measure/new column that tells me for each email received how long it took to reply.

Do I need to somehow merge the two excel sheets?

I think one of the issue is that it's not like the email (received) and email (sent) are necessarily on the same row so I need the software to "dig" through all the data to find the correct email (by example the received email would have the name "hello casablanca" and the reply must be "RE:hello casablanca" and differentiate in the case of a chain of email between "RE:hello casablanca" and "RE:RE:hello casablanca" if you see what I mean)

amitchandak
Super User
Super User

@Aymeric_2350 , check if Text filter can help

Text Filter Slicer and how to search on Multiple columns: https://youtu.be/RbeZRJ3uAZE

 

or create measures using

CONTAINSSTRING and CONTAINSSTRINGEXACT: https://www.youtube.com/watch?v=XbgLGDvWdWQ&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=44

 

SEARCH and FIND: https://www.youtube.com/watch?v=mZt0HJw4gjQ&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=45

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.