Calculating Response time between dealer and user response comments

[Sharma0815]

Hi Everyone,

I have a dataset where I am trying to calculate the user's first response time to every dealer's comment to a case file using the comment date-time. I've calculated this in hive using lag, lead, and min window functions, but I am trying to calculate this in Power BI.

Any help would be greatly appreciated.

Here is my data looks like, I've added a response time column which I manually calculated in excel.

I've created this below column to get the dealer update time.

I am trying to get this same type of first user response timestamp to dealer comments to calculate the response time 

Dealer Update Time = CALCULATE(
MIN(Query1[Comment Date_Time]),
FILTER(ALLEXCEPT(Query1,Query1[Case#],Query1[action_type],Query1[note]),
Query1[action_type] in {
"DEALER_COMMENT"}
))

Case#	Comment date_time	lastuser	action_type	note	Response time
4815	2020-09-02 10:08:26.85	NULL	DEALER_COMMENT	i360 Update - Your
4815	2020-09-02 17:17:08.987	USER1	USER_COMMENT	Bodyshop please ad	7.14
4815	2020-09-05 09:20:27.673	NULL	DEALER_COMMENT	i360 Update - Than
4815	2020-09-09 16:08:54.44	USER2	USER_COMMENT	Spoke with Robert	102.89
4815	2020-09-15 10:08:30.023	USER3	USER_COMMENT	Called the body sh
4815	2020-09-17 11:47:28.327	NULL	DEALER_COMMENT	i360 Update - Than
4815	2020-09-17 16:09:38.44	USER4	USER_COMMENT	Spoke with Robert	4.36
4815	2020-09-22 12:07:32.877	USER5	USER_COMMENT	Spoke with Brian a
4815	2020-10-01 09:17:44.543	USER6	USER_COMMENT	Called the body sh
4815	2020-10-01 14:06:21.807	USER7	USER_COMMENT	Spoke with Rick in
4815	2020-10-06 14:37:24.457	NULL	DEALER_COMMENT	Per Brian, repairs
4815	2020-10-12 12:10:14.377	NULL	DEALER_COMMENT	Per Robert, the ba
4815	2020-10-13 08:16:23.117	USER8	USER_COMMENT	Spoke with Robert	161.64
4815	2020-10-13 15:17:31.12	NULL	DEALER_COMMENT	Per Robert, wires
4815	2020-10-15 09:53:16.16	USER9	USER_COMMENT	Spoke with Michael	42.59

[CNENFRNL]

Hi, @Sharma0815 , you might want to try such a solution in Power Query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ndNda4MwFAbgv3Lw2kI+jB+5Wzvv1m3042KUMtIaMFSqqKPs3++kViZr2jJBEEQezvueZLPxgpgKz/cYYWRCkglhQIkksWQRfl0v0wXF93P69JIuPmdv83n6usIPhocE1lWmWg0T+Ci/am/rO7FI4kOSAWbfA2paZt9NXlZQFVo1GlTmpASQRDIiWXwh2OO5Vrk6OrEEaGhDimCA/ZlrWZUHDSfT5rAod7puwUVRcemLkwvBr6mZKgqdQZtr2GFaaHInFQGlMoh+IwbjI1oMIyaSD7FRERlukUkSSd5HE3epaW3UEdQVRVGjdot4IIKeCEe11VE0kCSUrN9ddD+g2R/AXHdlqdBSHIvvO4pdxb/rusvmQ60rZerGhdFzW3gm6EOs69zvkjr7ohzwaOEiGR9g/99iR1Fhq+f9HUwezXUytW7c2Pk2Co6jDbDbc83NPle6uEFhWfZi95Sdzz6un8+bcv68/QE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Case#" = _t, #"Comment date_time" = _t, lastuser = _t, action_type = _t, note = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Case#", Int64.Type}, {"Comment date_time", type datetime}, {"lastuser", type text}, {"action_type", type text}, {"note", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","",null,Replacer.ReplaceValue,{"lastuser"}),
    #"Filled Up" = Table.FillUp(#"Replaced Value",{"lastuser"}),

    #"Grouped Rows" = Table.Group(#"Filled Up", {"Case#", "lastuser"}, {{"All", each _}}, GroupKind.Local),
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Case#", "lastuser"}),
    Trf = Table.TransformColumns(#"Removed Columns",
        {{"All", each
            Table.AddColumn(_, "Response Time", (row) =>
                [
                    Time_min = List.Min([Comment date_time]),
                    Time_max = List.Max([Comment date_time]),
                    Diff =Number.Round(Duration.TotalHours(Time_max - Time_min), 2),
                    result = if row[Comment date_time] = Time_max and Table.RowCount(_) > 1 then Diff else ""
                ][result]
            )
        }}
    ),
    #"Expanded All" = Table.ExpandTableColumn(Trf, "All", {"Case#", "Comment date_time", "lastuser", "action_type", "note", "Response Time"}, {"Case#", "Comment date_time", "lastuser", "action_type", "note", "Response Time"})
in
    #"Expanded All"