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.
Hi everyone!
I need to create a new column that: Return 0 if the same person calls again and 10 minutes have not passed since the first call. Otherwise, return 1.
I attached an example to understand it better:
Hope someone can help me.
Cheers!
Solved! Go to Solution.
@Anonymous , Try like
In 10 Min = var _last = maxx(FILTER('DATOS CALL CENTER (2)', 'DATOS CALL CENTER (2)'[Cola] = EARLIER([Cola]) && [Rank] = EARLIER([Rank]) -1),[Fecha]) var _secondLast = maxx(FILTER('DATOS CALL CENTER (2)', 'DATOS CALL CENTER (2)'[Cola] = EARLIER([Cola]) && [Rank] = EARLIER([Rank]) -2),[Fecha]) var _diff1 = DATEDIFF(_last, [Fecha],SECOND)/60 var _diff2 = DATEDIFF(_secondLast, [Fecha],SECOND)/60 var _diff3 = DATEDIFF(_secondLast, _last,SECOND)/60 return SWITCH(TRUE(), ISBLANK(_diff1) , 1 , _diff1 >=10, 1, _diff3>10 && _diff1 <10, 0, _diff1<10 && _diff2 >10 && _diff3< 10 , 1 , 0 )
You can create a calculated column
Recalls =
var _firstCallTime =
CALCULATE(
MIN('yourTable'[Time]),
ALLEXCEPT('yourTable', 'yourTable'[Calls])
)
var _elapsedTime =
[Time] - _firstCallTime
var _elapsedTimeFormatted =
(HOUR(_elapsedTime)*60) + MINUTE(_elapsedTime)
Return
SWITCH(
TRUE(),
[Time] = _firstCallTime, 1,
_elapsedTimeFormatted < 10, 0,
1
)
And you would end up with
Proud to be a Super User! | |
@jgeddes thanks for answering. I tried your dax code but isn't working. I don't know why.
I'm gonna share with you my file: https://drive.google.com/file/d/1BzCJ-3gKAEVkkIHtjpN4LnWqRBUR0cQb/view?usp=share_link where "Cola" = Calls, "Tiempo inicio" = Time.
Thanks
@Anonymous , Try like
In 10 Min = var _last = maxx(FILTER('DATOS CALL CENTER (2)', 'DATOS CALL CENTER (2)'[Cola] = EARLIER([Cola]) && [Rank] = EARLIER([Rank]) -1),[Fecha]) var _secondLast = maxx(FILTER('DATOS CALL CENTER (2)', 'DATOS CALL CENTER (2)'[Cola] = EARLIER([Cola]) && [Rank] = EARLIER([Rank]) -2),[Fecha]) var _diff1 = DATEDIFF(_last, [Fecha],SECOND)/60 var _diff2 = DATEDIFF(_secondLast, [Fecha],SECOND)/60 var _diff3 = DATEDIFF(_secondLast, _last,SECOND)/60 return SWITCH(TRUE(), ISBLANK(_diff1) , 1 , _diff1 >=10, 1, _diff3>10 && _diff1 <10, 0, _diff1<10 && _diff2 >10 && _diff3< 10 , 1 , 0 )
@Anonymous , check if this can help
Check if this solution can work.
new column =
var _max = maxx(filter( Table, [calls] = earlier([call]) && [Time] <earlier(Time) ), [Time])
return if( isblank(_max) || datediff(_max, [Time], second)/60 > 10, 1 ,0)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
98 | |
78 | |
65 | |
53 |
User | Count |
---|---|
144 | |
103 | |
98 | |
85 | |
64 |