Forum Discussion
To show max value based on condition within time period
Hello!
Could you, please, help me with the following task.
I have the set of data:
| Phone | Date | Call number CC | Max Call number CC |
| 111 | 01-11-23 12:59 | 1 | 1 |
| 111 | 15-11-23 12:59 | 1 | 5 |
| 111 | 21-11-23 15:56 | 2 | 5 |
| 111 | 23-11-23 14:05 | 3 | 5 |
| 111 | 27-11-23 17:09 | 4 | 5 |
| 111 | 28-11-23 15:09 | 5 | 5 |
| 222 | 24-12-23 11:24 | 1 | 3 |
| 222 | 29-12-23 15:00 | 2 | 3 |
| 222 | 03-01-24 17:44 | 3 | 3 |
| 111 | 27-01-24 11:00 | 1 | 4 |
| 111 | 02-02-24 19:01 | 2 | 4 |
| 111 | 06-02-24 20:23 | 3 | 4 |
| 111 | 07-02-24 16:31 | 4 | 4 |
| 333 | 10-02-24 16:28 | 1 | 1 |
| 333 | 20-02-24 12:28 | 1 | 1 |
"Call number CC" calculates serial number of call (number of repeated call) if it happens from the same "Phone" within 7 days.
If it' happens after 7 days - count starts again from 1. This column works fine.
What I need is to show maximum number of repeated calls from the same phone within time period of 7 days.
Please, see the expected result in the column "Max Call number CC"
Thank you !
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.
16 Replies
- lbendlin
Super User
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.
- Ashish_Mathur
Super User
Hi,
Has the Call CC number column been created in the Power Query Editor? Are you looking for a Power Query or a calculated column DAX solution?
- Dimi_2207
Helper I
Hello,
"Call number CC" column has been created in DAX. Yes, i am looking for a calculated column in Dax for "Max Call Number CC"
- Ashish_Mathur
Super User
Hi,
Try these calculated column formulas
Date of next phone = CALCULATE(MIN(Data[Date]),FILTER(Data,Data[Phone]<>EARLIER(Data[Phone])&&Data[Date]>EARLIER(Data[Date])))Column = LOOKUPVALUE(Data[Phone],Data[Date],CALCULATE(MIN(Data[Date]),FILTER(Data,Data[Date]>EARLIER(Data[Date])&&Data[Call number CC]=EARLIER(Data[Call number CC]))))Max cc number = if(and(Data[Phone]=Data[Column],Data[Call number CC]=1),Data[Call number CC],COALESCE(CALCULATE(max(Data[Call number CC]),FILTER(Data,Data[Phone]=EARLIER(Data[Phone])&&Data[Date]>=EARLIER(Data[Date])&&Data[Date]<=Data[Date of next phone]-time(0,0,1))),1))Hope this helps.
- AnonymousNot applicable
Hi Dimi_2207 ,
Based on the information you have provided, and the ideas provided by lbendlin and Ashish_Mathur , you can follow the steps below to solve your problem:
1. Add an index column.
2. Add new columns.datediff = VAR _1 = CALCULATE ( MAX ( 'Table'[Date] ), FILTER ( 'Table', 'Table'[Index] = EARLIER ( 'Table'[Index] ) - 1 && 'Table'[Phone] = EARLIER ( 'Table'[Phone] ) ) ) RETURN DATEDIFF(_1,'Table'[Date],DAY)Max Call number CC = VAR _1 = CALCULATE ( MAX ( 'Table'[Call number CC] ), FILTER ( 'Table', 'Table'[Phone] = EARLIER ( 'Table'[Phone] ) && DATEDIFF ( 'Table'[Date], EARLIER ( 'Table'[Date] ), DAY ) <= 7 ) ) VAR _2 = CALCULATE ( MAX ( 'Table'[datediff] ), FILTER ( 'Table', 'Table'[Index] = EARLIER ( 'Table'[Index] ) + 1 ) ) RETURN IF ( _2 - 'Table'[datediff] > 7, 1, _1 )Final output:
How to Get Your Question Answered Quickly - Microsoft Fabric Community
If it does not help, please provide more details with your desired out put and pbix file without privacy information.
Best Regards,
Ada Wang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- lbendlin
Super User
Please note that the use of EARLIER or EARLIEST is discouraged. Preference should be given to variables.
- Dimi_2207
Helper I
Thank you! Works very fast, but I've tried this with my set (please, see in the attach Power BI file) and it shows wrong data. Could you, please, check what am i doing wrong?