Forum Discussion
To show max value based on condition within time period
- 2 years ago
let Source = Table.FromRows( Json.Document( Binary.Decompress( Binary.FromText( "ddBBDsQgCIXhq0xc1wQeqJWrNL3/NUY70tLFJO6+/BA8jsTMaUvEmTlDPgwrPZ2bA5c/gLsoVmoEcVCjEqE5NKPXqP0ZtQDABM2MC9igEbrDKCgASR6nQOcO1ffyBezFuhx5vAndiCPUBSCDRGheVJNfISLzr+gB7AFwAy44vw==", BinaryEncoding.Base64 ), Compression.Deflate ) ), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Phone = _t, Date = _t] ), #"Changed Type" = Table.TransformColumnTypes( Source, {{"Phone", Int64.Type}, {"Date", type datetime}}, "en-GB" ), #"Grouped Rows" = Table.Group( #"Changed Type", {"Phone"}, {{"Rows", each _, type table [Phone = nullable number, Date = nullable datetime]}} ), Process = (tbl) => let #"Sorted Rows" = Table.Sort(tbl, {{"Date", Order.Ascending}}), #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type), #"Added Custom" = Table.AddColumn( #"Added Index", "Group", each List.Accumulate( {0 .. [Index]}, 1, (state, current) => if current = 0 then state else if #"Added Index"{current}[Date] - #"Added Index"{current - 1}[Date] > #duration(7, 0, 0, 0) then state + 1 else state ) ), #"Removed Other Columns" = Table.SelectColumns(#"Added Custom", {"Phone", "Date", "Group"}), #"Grouped Rows1" = Table.Group( #"Removed Other Columns", {"Group"}, { { "Rows", each Table.AddIndexColumn(_, "Index", 1, 1, Int64.Type), type table [ Phone = nullable number, Date = nullable datetime, Group = number, Index = Int64.Type ] }, {"Max", each Table.RowCount(_), Int64.Type} } ), #"Expanded Rows" = Table.ExpandTableColumn( #"Grouped Rows1", "Rows", {"Phone", "Date", "Index"} ) in #"Expanded Rows", #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Processed", each Process([Rows])), #"Expanded Processed" = Table.ExpandTableColumn( #"Added Custom", "Processed", {"Group", "Date", "Index", "Max"}, {"Group", "Date", "Index", "Max"} ), #"Removed Other Columns" = Table.SelectColumns( #"Expanded Processed", {"Phone", "Group", "Date", "Index", "Max"} ) in #"Removed Other Columns"How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.
Thank you! Made the change.
Unfortunately, still refresh takes from 5 to 10 minutes.
Is it possible to do something similar in DAX, may be it will be faster ?
your sample file only has 12 rows. Please provide a sample file that illustrates the issue.
- Dimi_22072 years ago
Helper I
Hello,
Kindly accept the sample file of Power BI where the issue is reproduced.
I've renamed column "Phone" in "ID" as the last step.
Also, i've merged data in query called "Table 2" with query called "Table".
"Query 1" - is the blank query where i've put the code you've published (with my previous small "sample" table that works fast).
I am attaching excel file that i am using as source for this Power BI file with random customer IDs (phones) to show the issue i have with the real data that is structured the same way.
Thank you !!!
- lbendlin2 years ago
Super User
I don't know how to correlate the tables in the Excel file. Please explain.
The PBIX is not reachable.
- Dimi_22072 years ago
Helper I
In excel i have 2 tables.
Table 1 contains "Customer ID" column this is the phone numbers whom which customers called.
Column "Local Start Time" this is Date/Time when a call took place.
I've implemented the code you proposed for this table to show number of call attempt within 7 days (column "Index") and display max call attempt within 7 days for each row in separate columns (column "Max").
Table 2 in excel file - is the table to which i added new columns "Index" and "Max" by merging it in Power Query with Table 1 by column "Customer ID".
Could you, please, check if Power BI file is accessible from this link ?
Sorry, that my explanations was not clear initially and thank you !