Subtract date/time based on text contained within a string

[Anonymous]

I have a ticketing system that audits field changes on ticket records. I am doing some research on how long it takes tickets to be raised in priority from low or medium to high or critical. I am also researching the total time it take a ticket from the time it is created to the time it reaches its final priority. Date examples with additional explaination below. My desired results are in hours rounded to 1 decimal.

My audit record table. I need to only look for the priority related changes within the "Field Changes" column (in bold).

AuditDate	IncidentID	Field Changes
2/26/2017 14:56	INC000001771984	Status: Assigned  (1)~Status_Reason: ~Urgency: 2-High  (2000)~Impact: 2-Significant/Large  (2000)~Priority: High  (1)~Priority Weight: 301
2/27/2017 16:44	INC000001771984	Status: Assigned  (1)~Priority Weight: 201~Urgency: 3-Medium  (3000)~Impact: 3-Moderate/Limited  (3000)~Priority: Medium  (2)
3/2/2017 12:37	INC000001771984	Priority Weight: 301~Urgency: 2-High  (2000)~Impact: 2-Significant/Large  (2000)~Priority: High  (1)~
5/15/2017 14:49	INC000001836727	Status: In Progress  (2)~Priority Weight: 201~Impact: 3-Moderate/Limited  (3000)~Priority: Medium  (2)
5/15/2017 15:13	INC000001836727	Priority Weight: 301~Urgency: 2-High  (2000)~Impact: 2-Significant/Large  (2000)~Priority: High  (1)~
5/15/2017 15:34	INC000001836727	Priority Weight: 201~Urgency: 3-Medium  (3000)~Impact: 3-Moderate/Limited  (3000)~Priority: Medium  (2)~
10/31/2017 16:57	INC000001996080	Priority Weight: 301~Urgency: 2-High  (2000)~Impact: 2-Significant/Large  (2000)~Priority: Low  (3)~
11/1/2017 18:37	INC000001996080	Priority Weight: 201~Urgency: 3-Medium  (3000)~Impact: 3-Moderate/Limited  (3000)~Priority: Medium  (2)~
11/1/2017 18:43	INC000001996080	Priority Weight: 301~Urgency: 2-High  (2000)~Impact: 2-Significant/Large  (2000)~Priority: High  (1)~
11/12/2017 21:13	INC000002007779	Priority Weight: 201~Urgency: 3-Medium  (3000)~Impact: 3-Moderate/Limited  (3000)~Priority: Medium  (2)~
11/14/2017 15:44	INC000002007779	Priority Weight: 301~Urgency: 2-High  (2000)~Impact: 2-Significant/Large  (2000)~Priority: Critical  (0)~
1/3/2018 17:42	INC000002059015	Priority Weight: 301~Urgency: 2-High  (2000)~Impact: 2-Significant/Large  (2000)~Priority: Low  (3)~
1/3/2018 18:56	INC000002059015	Priority Weight: 201~Urgency: 3-Medium  (3000)~Impact: 3-Moderate/Limited  (3000)~Priority: Medium  (2)~
1/3/2018 19:13	INC000002059015	Priority Weight: 301~Urgency: 2-High  (2000)~Impact: 2-Significant/Large  (2000)~Priority: High  (1)~
1/3/2018 20:13	INC000002059015	Priority Weight: 301~Urgency: 2-High  (2000)~Impact: 2-Significant/Large  (2000)~Priority: Critical  (0)~

My second table already has the ticket ID listed (one record per ticket).

For the "Time to Critical/High" colum I need the time between the first record of each ticket to the first record that is high or critical in the field changes column below. If the first record of the ticket is high or critical then the time would be zero. If the ticket has 4 audit records (low, medium, high, then critical), then I would need the time between the low and high records. 

For the "Total Time" column, I simply need the time between the first and last record of each ticket regardless or priority.

IncidentID	Time to High/Critical	Total Time
INC000001771984	0	93.7
INC000001836727	0.4	0.7
INC000001996080	25.8	25.8
INC000002007779	42.5	42.5
INC000002059015	1.5	2.5

[v-lili6-msft · Accepted solution]

hi, @Anonymous

After my research, you could do these as below:

Step1:

Use SEARCH or FIND Function to add a column that based on text contained.

contained = 
IF (
    SEARCH ( "Priority: Critical", 'Table'[Field Changes], 1, 0 ) > 0,
    0,
    IF (
        SEARCH ( "Priority: High", 'Table'[Field Changes], 1, 0 ) > 0,
        1,
        IF (
            SEARCH ( "Priority: Medium", 'Table'[Field Changes], 1, 0 ) > 0,
            2,
            IF ( SEARCH ( "Priority: Low", 'Table'[Field Changes], 1, 0 ) > 0, 3 )
        )
    )
)

Note: Pay attention to the standard text format

Step2:

Add two measure by these formulas

Time to High/Critical =
VAR _mindatetime0or1 =
    CALCULATE (
        MIN ( 'Table'[AuditDate] ),
        ALLEXCEPT ( 'Table', 'Table'[IncidentID] ),
        FILTER ( 'Table', 'Table'[contained] = 0 || 'Table'[contained] = 1 )
    )
RETURN
    DATEDIFF (
        CALCULATE (
            MIN ( 'Table'[AuditDate] ),
            ALLEXCEPT ( 'Table', 'Table'[IncidentID] )
        ),
        _mindatetime0or1,
        MINUTE
    )
        / 60

Total Time =
DATEDIFF (
    CALCULATE (
        MIN ( 'Table'[AuditDate] ),
        ALLEXCEPT ( 'Table', 'Table'[IncidentID] )
    ),
    CALCULATE (
        MAX ( 'Table'[AuditDate] ),
        ALLEXCEPT ( 'Table', 'Table'[IncidentID] )
    ),
    MINUTE
)
    / 60

Step3:

Format the decimal for two measure

Step4:

Drag  IncidentID and two measure into visual

here is pbix, please try it.

https://www.dropbox.com/s/7t1gtd35nfwaoux/Subtract%20datetime%20based%20on%20text%20contained%20within%20a%20string.pbix?dl=0

Best Regards,

Lin