Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Hi @niark
Could you explain more about your expected output?How to get the answer:2,4285714285714284 in your sample?
Regards,
Cherie
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |