DAX for matching string and calculate TAT

[vaibhavtandon87]

Hello all, 

A bit complex requirement which I am not able to resolve:

Basis UI path automation I would be getting data of emails from different folders in a table. An email will have multiple occurrences but with appended subject like 'Re', 'FW', "Re:Re:' etc as people would reply to an email that is in the inbox. I need to calculate:

1) Turnaround time of an email which essentially means if there are 2 emails of the same subject( subject would be appended with Re, FW etc in case of replies and forwards), TAT would be timestamp of last instance minus first instance.

2) Ageing - If there is only 1 instance then now minus timestamp is ageing.

Below is the table with multiple occurence of the email and timestamp associated.

Please let me know in case anybody needs any further explanation or if its not clear.

Subject	Received Time
PREM | Power BI Premium | Having problems with SharePoint list.  When trying connect to SharePoint list through Power BI desktop, he’s getting an error.	12/26/2018 23:00
RE: PREM | Power BI Premium | Having problems with SharePoint list.  When trying connect to SharePoint list through Power BI desktop, he’s getting an error.	12/26/2018 22:54
Re: Q4 Failure	12/24/2018 15:08
RE: PREM | Power BI Premium | Having problems with SharePoint list.  When trying connect to SharePoint list through Power BI desktop, he’s getting an error.	12/24/2018 14:42
FW: Q4 Failure	12/24/2018 14:07
RE:  License key information	12/24/2018 10:44
RE:  License key information	12/24/2018 10:12

[Anonymous]

First of all, you should correctly prepare your data for analysis using Power Query, not DAX. What this means is you should analyze your requirements (which measures you need to create and what attributes you want to slice by) and then create all the entities. For instance, you need to have a column that will store the "normalized subject" (without the appended RE: and the likes). You should probably create a table that will store EmailID and then all the varieties of it with the times when they arrived... Only after you have a proper model, you can think about creating performant and SIMLPE measures. In the current state of the model... JUST FORGET IT (unless you want to suffer).

Best
Darek

Best
Darek

[vaibhavtandon87]

Hello Darek,

Thanks for the suggestion! Could you please guide me on how to do with the Power query?

Regards

Vaibhav

[Anonymous]

https://community.powerbi.com/t5/Desktop/What-are-the-best-DAX-Power-Query-M-Language-training-resou...