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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
equlibrum52
Frequent Visitor

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

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_idCall_start timeCustomer IDCall statusAttempt
110.06.2022 09:22C1picked up1
210.06.2022 09:40C1missed1
310.06.2022 09:45C1missed2
411.06.2022 11:45C1picked up3
512.06.2022 15:45C1missed1
613.06.2022 15:45C2missed1
714.06.2022 11:45C2missed2
815.06.2022 10:45C2picked up3
915.06.2022 11:45C2picked up1

 

1 ACCEPTED SOLUTION
SpartaBI
Community Champion
Community Champion

@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

 

 


2022-05-09 22_36_04-Power BI Workspace Datasets License Permissions - Microsoft Power BI Community.png

Showcase Report – Contoso By SpartaBI


SpartaBI_3-1652115470761.png   SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Full-Logo11.png

View solution in original post

6 REPLIES 6
equlibrum52
Frequent Visitor

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: /

@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
Solution Specialist
Solution Specialist

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
Community Champion
Community Champion

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"

CNENFRNL_0-1654888537809.png

 

A showcase of powerful Excel worksheet formula,

CNENFRNL_1-1654889419167.png


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

AlB
Super User
Super User

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] )
    )

 

SU18_powerbi_badge

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
Community Champion
Community Champion

@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

 

 


2022-05-09 22_36_04-Power BI Workspace Datasets License Permissions - Microsoft Power BI Community.png

Showcase Report – Contoso By SpartaBI


SpartaBI_3-1652115470761.png   SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Full-Logo11.png

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.