Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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.

Reply
Dimi_2207
Helper I
Helper I

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: 

 

PhoneDateCall number CCMax Call number CC
11101-11-23 12:5911
11115-11-23 12:5915
11121-11-23 15:5625
11123-11-23 14:0535
11127-11-23 17:0945
11128-11-23 15:0955
22224-12-23 11:2413
22229-12-23 15:0023
22203-01-24 17:4433
11127-01-24 11:0014
11102-02-24 19:0124
11106-02-24 20:2334
11107-02-24 16:3144
33310-02-24 16:2811
33320-02-24 12:2811

 

"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 !

1 ACCEPTED SOLUTION
lbendlin
Super User
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.

View solution in original post

16 REPLIES 16
v-yifanw-msft
Community Support
Community Support

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.

vyifanwmsft_0-1708913928742.png


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:

vyifanwmsft_1-1708913973902.png

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.

 

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? 

 

Sample.pbix

 

Dimi_2207_0-1709594216087.png

 

Please note that the use of EARLIER or EARLIEST is discouraged. Preference should be given to variables.

Ashish_Mathur
Super User
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?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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"

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.

Ashish_Mathur_0-1709018801931.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
lbendlin
Super User
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.

Thank you for this ! Is it possible to replicate this in DAX? 

I have calculated column "Call number CC" that was created in DAX and it's not visible in Power Query Editor

 

That's the "Index"  column in my result. Feel free to rename it.

Thank you very much for your help and the code. 

This works and shows the expected results! 

The only thing is that it takes significant amount of time to work (about 3 minutes for a base i have currently of 32 000 rows. 

I've realized that it takes the time at the step "Expanded Processed":

Dimi_2207_1-1709569920710.png

 

Is there any chance to optimize it ?

Thank you again !

 

 

Try changing 

#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Processed", each Process([Rows])), 

to 

#"Added Custom" = Table.Buffer(Table.AddColumn(#"Grouped Rows", "Processed", each Process([Rows]))), 

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.

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 !!!

Sample 2.pbix

Sample Report.xlsx

I don't know how to correlate the tables in the Excel file.  Please explain.

 

The PBIX is not reachable.

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 !

 

Sample 2.pbix

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors