Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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 |
Solved! Go to 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
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 🙂
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
)
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,
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! |
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. |
@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
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
25 | |
18 | |
17 | |
16 | |
16 |
User | Count |
---|---|
28 | |
27 | |
18 | |
14 | |
13 |