Average number of calls before answer

[niark]

Hi all,

I have to answer a "simple" question which is "what is the average number of missing calls per caller before it is answered?"

I have an powerquery code to answer that but the query is really (really) long to process (the full table has 82K rows). I wanted to move that to DAX to verify if the performance is better but I don't know how to proceed with running/handling functions.

Here a sample of the data I have, 480 means missing call and 200 means answered call

CallerUri	        InviteTime	                ResponseCode
User1@contoso.com	2018-12-07T08:43:57.3070000	480
User1@contoso.com	2018-12-07T08:43:58.3070000	200
User1@contoso.com	2018-12-11T15:50:56.6430000	200
User1@contoso.com	2019-01-07T16:08:46.9200000	480
User1@contoso.com	2019-01-07T16:10:51.3130000	480
User1@contoso.com	2019-01-07T16:50:56.4500000	200
User2@contoso.com	2018-10-31T07:58:42.8270000	480
User2@contoso.com	2018-10-31T07:58:42.8330000	200
User2@contoso.com	2018-11-06T08:06:54.4230000	200
User2@contoso.com	2018-11-06T08:06:54.4270000	480
User2@contoso.com	2018-11-06T08:07:07.2340000	480
User2@contoso.com	2018-11-06T08:30:34.2890000	480
User2@contoso.com	2018-11-09T08:39:23.5700000	200

The answer for this sample is 2,4285714285714284

here the M code :

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ndK7bsMwDAXQf/EcE5cPvTj1I5wp8GR0bA00/X9UToEgg5LIFrQJB7wkdbkM5+vnD38s6/fvel1pWb+G0yDgPLKMSBOym3pIpEiop75axjCfemV+kIL3knni4AEeIkXTPllG8FaTo29lIxVBX9oHybUmk7LulnJY/vdpAY0+pTkhjMoTUh2sm1CW1la6pLZm25Y1bdz2iRrYyOS47E97l6leErXdUuFqJLnskeUmi4tSSK2t6DOZp/qBEBxKKK20ryQfkPJE3tLOfw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [CallerUri = _t, InviteTime = _t, ResponseCode = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"CallerUri", type text}, {"InviteTime", type datetime}, {"ResponseCode", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each fn(#"Changed Type",[CallerUri], [InviteTime])),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"InviteTime"}, {"InviteTime.1"}),
#"Grouped Rows" = Table.Group(#"Expanded Custom", {"InviteTime.1"}, {{"Count", each Table.RowCount(_), type number}}),
#"Calculated Average" = List.Average(#"Grouped Rows"[Count])
in
#"Calculated Average"

and here the code for the function :

(TableName as table, Caller as text, Date as datetime) =>

let 
    #"Filtered Rows" = Table.SelectRows(TableName, each ([CallerUri] = Caller and (([ResponseCode] = 200 and [InviteTime] >= Date)))),
    #"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"InviteTime", Order.Ascending}}),
    #"Kept First Rows" = Table.FirstN(#"Sorted Rows",1),
    result = Table.SelectColumns(#"Kept First Rows",{"InviteTime"})
in
    result

Maybe the power query function could also be optimized, I take any tips 🙂

Can somebody help me ?

Thanks

[v-cherch-msft]

Hi @niark

Could you explain more about your expected output?How to get the answer:2,4285714285714284 in your sample?

Regards,

Cherie

[niark]

Hi @v-cherch-msft,

The power query code provides you the steps to have the result.

Here an excel view of what I need :

 the rows are grouped by user and by calls beginning by the missing ones (480) until the first succeeded one (200) then a count is done and the average of this column is calculated (the formula is what provides the result). so for this sample, a user has to call 2.42 times to get an anwser.

Hope it's clearer.