How to count the number of connection attempts to make the first successful call

[equlibrum52]

Hello I really need your help. I would like to create a calculated column that would count the number of attempts we needed to contact a unique customer in a call center. However, it is important for me that the counter restarts after each successful attempt to reach the customer (when call_status = picked up). So for each conversation of a given client, I would like to know which is the attempt to reach him after the last successful conversation.

Here is an example of such data and the calculated column I would like to get - "Attempt"

I will be very grateful for any help 🙂

Call_id	Call_start time	Customer ID	Call status	Attempt
1	10.06.2022 09:22	C1	picked up	1
2	10.06.2022 09:40	C1	missed	1
3	10.06.2022 09:45	C1	missed	2
4	11.06.2022 11:45	C1	picked up	3
5	12.06.2022 15:45	C1	missed	1
6	13.06.2022 15:45	C2	missed	1
7	14.06.2022 11:45	C2	missed	2
8	15.06.2022 10:45	C2	picked up	3
9	15.06.2022 11:45	C2	picked up	1

[equlibrum52]

Hi everyone, thank you for all the answers. Dax is amazing that there are so many different ways to approach the same problem.

@SpartaBI  Your solution is the best, it is a calculated column and it is very well optimized, thank you very much for your help.

@AlB  really thanks for the answer, unfortunately your approach has performance issues and I haven't been able to apply your formula to my large dataset of over 2 million conversations.

 @vapid128 vapid128 you are absolutely right your code runs very fast for large datasets, but unfortunately the result is a little different than expected. That is, with your code, any picked up call resets counter, so all picked up calls are Attempt = 1. However, I would like this counter to restart after each successful call, and not with each successful call.

@CNENFRNL - thank you also for your answer, the result of your code is correct, but I really wanted it to be a calculated column. By the way, with my dataset, finally refreshing this table also took a lot of time: /

[SpartaBI]

@equlibrum52 my pleasure 🙂 and thank you for taking the time to check each solution everyone gave you and respond to each one of the amzaing community members who tried to help. This is really appreciated. 

Hey, check out my showcase report - got some high level stuff there 🙂
https://community.powerbi.com/t5/Data-Stories-Gallery/SpartaBI-Feat-Contoso-100K/td-p/2449543
Give it a thumbs up over there if you liked it 🙂

[vapid128]

WOW, my top secret function will help.

This function work with large dateset.

This function only work with DESC.

my funcion1

Timesindex = RANKX(FILTER(orders,orders[user_id]=EARLIER(orders[user_id]) && orders[LastDays]>30),orders[done_at],orders[done_at],DESC)

my function 2 //make it asc order

Time2 = RANKX(FILTER(orders,orders[user_id]=EARLIER(orders[user_id]) && orders[Timesindex]=EARLIER(orders[Timesindex])),orders[done_at],orders[done_at],ASC)

first function for you:

Timesindex = 
RANKX(
    FILTER(
        orders,
        Table1[Customer ID] = EARLIER(Table1[Customer ID]) && 
        Table1[Call status] = "picked up"
    ),
    Table1[Call_start time],
    Table1[Call_start time],
    DESC
)

Second Function for you

Attempt = 
RANKX(
    FILTER(
        orders,
        Table1[Customer ID] = EARLIER(Table1[Customer ID]) && 
        orders[Timesindex] = EARLIER(orders[Timesindex])
    ),
    Table1[Call_start time],
    Table1[Call_start time],
    ASC
)

[CNENFRNL]

For fun only, a showcase of powerful Table.Group() in PQ; and the Call_id is redundant.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc5LCoAwDEXRrUjGUpLY+pu6jOLIdlBEEMT9awQ/tZ1eDnmxFghKIFRYK0bmArue+UyD9DVMs3fFvsJYWuBEarzlErbNu4tVKTMZpoXRw4g+LN6VTPxKkz9YC6tSxjFrhOl098daYeZl+GHxe91PUl6OBw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Call_id = _t, #"Call_start time" = _t, #"Customer ID" = _t, #"Call status" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Call_id", Int64.Type}, {"Call_start time", type datetime}, {"Customer ID", type text}, {"Call status", type text}}, "fr"),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", -1),

    Grouped = let l = List.PositionOf(#"Added Index"[Call status],"picked up",2) in Table.Group(#"Added Index", "Customer ID", {"grp", each Table.Group(_, "Index", {"gr", each Table.AddIndexColumn(_, "Attempt", 1)}, 0, (x,y) => Byte.From(List.Contains(l, y)))}),

    #"Expanded grp" = Table.ExpandTableColumn(Grouped, "grp", {"gr"}),
    #"Expanded gr" = Table.ExpandTableColumn(#"Expanded grp", "gr", {"Call_start time", "Call status", "Attempt"})
in
    #"Expanded gr"

A showcase of powerful Excel worksheet formula,

[AlB]

Hi @equlibrum52 

Try this calculated column. See it all at work in the attached file.

Attempt = 
VAR previousPU_ =
    CALCULATE (
        MAX ( Table1[Call_id] ),
        Table1[Call_id] < EARLIER ( Table1[Call_id] ),
        Table1[Call status] = "picked up",
        ALLEXCEPT ( Table1, Table1[Customer ID] )
    )
RETURN
    CALCULATE (
        COUNT ( Table1[Call status] ),
        Table1[Call_id] <= EARLIER ( Table1[Call_id] ),
        Table1[Call_id] > previousPU_,
        ALLEXCEPT ( Table1, Table1[Customer ID] )
    )

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[SpartaBI · Accepted solution]

@equlibrum52 
Create this calculated column:

Attempt Calculated Column = 
VAR _currnet_customer = 'Table'[Customer ID]
VAR _current_time = 'Table'[Call_start time]
VAR _last_picked_up_for_customer = COALESCE(MAXX(FILTER('Table', 'Table'[Customer ID] = _currnet_customer &&'Table'[Call_start time] < _current_time &&'Table'[Call status] = "picked up"),'Table'[Call_start time]),0)
VAR _result = 
    COUNTROWS(FILTER('Table', 'Table'[Customer ID] = _currnet_customer &&'Table'[Call_start time] <= _current_time &&'Table'[Call_start time] > _last_picked_up_for_customer)) 
RETURN
    _result

Showcase Report – Contoso By SpartaBI