Multiple date difference and date count in a string

[Anonymous]

Hi, can anyone help me with implementing this?

In my Dashboard I have 4 columns:

A) Number of the issue

B) Person Handling the issue

C) Creation Date of the issue

D) Comments on the issue

Can you help me with one or multiple DAX to:

1. Count the total number of the Comments placed for each issue, counting only the ones placed by the Handling Person.

2. Calculate how much time has passed between Creation Date of the issue and the date of the first comment placed by the Handling Person. Basically it can also be date of handling person's comment minus date of creator's comment + it needs to exclude weekend days.

3. When we have a longer conversation between the Creator and Handling Person, calculate how much time has passed between each comment of the Handling Person.

Here is how each cell looks like

Comments :Comments Column(text in a row)

Creation Date: Creation Date Column

Appreciate your help.

[Anonymous]

Hi @Anonymous ,

Any updates? We are willing to help!

You could refer to the blogs to learn
How to Get Your Question Answered Quickly - Microsoft Power BI Community

How to provide sample data in the Power BI Forum - Microsoft Power BI Community

Best Regards,
Eyelyn Qin

[truptis]

@Anonymous - You will need multiple splits.

1st split with " - " delimiter (space before and after - ) 
2nd split with "(E-mail address)" delimiter

3rd split with "(Comment)" 

[truptis]

Hi @Anonymous ,
for spliting your each cell into 4 columns, you can use Split in your Transform data:

After Splitting, click on group by:

Use your Person Handling column. 

Then create a column:
Result = Calculate(Count(Comments), [Handling_Person] = EARLIER([Handling_Person), [issue_no] = EARLIER(Issue_no), ALL(tablename))

By this, your 1st issue will be resolved.

[Anonymous]

@truptis , your solution to split the columns splits the text incorrectly. Are you sure that "/" should be the delimiter?

[amitchandak]

@Anonymous , I think you have split it a few times in power query based on various delimiters

Like first into 2 columns using -

then based on space or something which can give email

https://www.tutorialgateway.org/how-to-split-columns-in-power-bi/

[Anonymous]

@amitchandak , but I don't need to grab the e-mail. The most important thing is that I need to calculate the date difference between comments published for an issue.

[amitchandak]

@Anonymous ,
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[truptis]

@Anonymous -> for calculation date difference you can use DATEDIFF(date, comparison_date,DAYS). This will return the difference in dates in no. of days. eg- datediff(03/04/2021,04/04/2021,DAYS) will return 1