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
Anonymous
Not applicable

CALCULATE A NEW COLUMN WITH FUNCTION IF

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:

Captura.PNG

Hope someone can help me. 

 

Cheers! 

 

1 ACCEPTED 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 )

View solution in original post

5 REPLIES 5
jgeddes
Super User
Super User

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

jgeddes_0-1669672009344.png

 




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





Anonymous
Not applicable

@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. 

Anonymous
Not applicable

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)

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 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.

Top Solution Authors