Compare data with other rows

[dmartinezl]

Hi, everyone!

Thanks in advance for your help.

I'm stucked with the following problem:

I have this (partial) table with data from a call center.

My goal is to identify what calls are missed (abandoned) and there is an agent free.

I tried with this code (and many others...) to create the custom column AGENT FREE  but I have no clue (and no results...)

AGENT FREE = 
var star = [StartTime (s)]
var stop = [EndTime (s)]
var cc = COUNTX('Table';FILTER('Table';'Table'[Status]="Abandoned" && star<stop<star))
return
cc

Regards,

[az38]

@dmartinezl  and all

just for other user's information

the issue in the data source. there are a lot of the same starttime, because there are a lot of days, one starttime (s) -to many days.

and one more - there are a lot of rows with the same start date and time and status="Handled".

after fix it (create DateTime column and remove duplicate with the same start date time) you could use the next measure

AGENT FREE = if(
and(LOOKUPVALUE('Table'[EndDateTime];'Table'[StartDateTimeText];calculate(MAX('Table'[StartDateTimeText]); filter('Table';AND('Table'[Status]="Handled";'Table'[StartDateTimeText]<=EARLIER('Table'[StartDateTimeText])))))<'Table'[StartDateTimeText];
'Table'[Status]="Abandoned");
1;0)

do not hesitate to give a kudo to useful posts and mark solutions as solution

[az38 · Accepted solution]

Hi @dmartinezl 

I see solution if you have a field Agent

there are two options. it should give you the same result but it may have a different consumption. try

AGENT FREE = if(
and(LOOKUPVALUE('Table'[EndDateTime];'Table'[Agent];SELECTEDVALUE('Table'[Agent]);'Table'[StartDateTimeText];calculate(MAX('Table'[StartDateTimeText]); filter('Table';AND('Table'[Status]="Handled";'Table'[StartDateTimeText]<=EARLIER('Table'[StartDateTimeText])))))<'Table'[StartDateTimeText];
'Table'[Status]="Abandoned");
1;0)

or

AGENT FREE = if(
and(LOOKUPVALUE('Table'[EndDateTime];'Table'[StartDateTimeText];calculate(MAX('Table'[StartDateTimeText]); filter('Table';AND(and('Table'[Status]="Handled";'Table'[Agent]=selectedvalue('Table'[Agent]));'Table'[StartDateTimeText]<=EARLIER('Table'[StartDateTimeText])))))<'Table'[StartDateTimeText];
'Table'[Status]="Abandoned");
1;0)

do not hesitate to give a kudo to useful posts and mark solutions as solution

[dmartinezl]

Hi @az38 

It is not a data problem. It's normal to have duplicates because each row is a call and there may be several calls at once.

With this premise, your first proposed solution consumes too many resources and PowerBI breaks. The second works but only marks the abandoned calls regardless the startdatetime and enddatetime criteria...

[az38]

Hi @dmartinezl 

try a measure

= countrows(
FILTER('Table';
AND('Table'[Status]="Abandoned";'Table'[AGENT FREE?]=1)
)
)

do not hesitate to give a kudo to useful posts and mark solutions as solution
Linkedin

[dmartinezl]

Sorry @az38 but I think I have not explained well. There is no FREE AGENT column this is what I want to create.

Post modified! 

[az38]

@dmartinezl 

try

= countrows(
FILTER('Table';
AND('Table'[Status]="Abandoned";'Table'[StartTime (s)]='Table'[EndTime (s)])
)
)

do not hesitate to give a kudo to useful posts and mark solutions as solution
Linkedin

[dmartinezl]

Thanks @az38 but doesn't work. It only counts how many rows have Status=Abandoned

[az38]

@dmartinezl 

sorry, now I got it

try column

AGENT FREE? = 
if(
and(
LOOKUPVALUE('Table'[EndTime (s)];'Table'[StartTime (s)];
calculate(max('Table'[StartTime (s)]); filter('Table';and('Table'[Status]="Handled";'Table'[StartTime (s)]<EARLIER('Table'[StartTime (s)])))))<'Table'[StartTime (s)];
'Table'[Status]="Abandoned");
1;0)

do not hesitate to give a kudo to useful posts and mark solutions as solution
Linkedin

[dmartinezl]

Awesome @az38 !!!

I tried with my real data but you would know why it shows me the error "A table of multiple values was supplied where a single value was expected"?

Very thankful

[dax]

Hi dmartinezl,

I am not clear  about your logic, did you mean that when Abandoned exist twice, you want to mark the second as 1? If so, you could try to refer to my M code to see whether it work or not.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fc49CoAwDIbhu3QW/JLU+rO5eQfpoOgmev/NVgtViE4vKXloxtEwqC0JJbemMIQOCB2mfdnWJb5wnMUBd+PsC4VRaD8Hd+wXTMzlqkxURk3ukwkSq95Hyr0u6UjLH6xWf7P8zwgqc8j1/gQ=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, time = _t, status = _t, Duration = _t, ST = _t, ET = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type text}, {"time", type text}, {"status", type text}, {"Duration", type text}, {"ST", type text}, {"ET", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
    #"Grouped Rows" = Table.Group(#"Added Index", {"Date", "status"}, {{"all", each _, type table [Date=text, time=text, status=text, Duration=text, ST=text, ET=text, Index=number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([all],"aindex",1,1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"all"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"time", "Duration", "ST", "ET", "aindex"}, {"Custom.time", "Custom.Duration", "Custom.ST", "Custom.ET", "Custom.aindex"}),
    Custom1 = Table.ReplaceValue(#"Expanded Custom",each [Custom.aindex],each if[Custom.aindex] <> 1 and [status]="Abandoned" then 1 else 0,Replacer.ReplaceValue,{"Custom.aindex"})
in
    Custom1

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[dmartinezl]

Hi @dax 

It may happen that an operator is free but nevertheless the call is marked abandoned.

The objective is to identify these calls with the help of the columns StartTime and EndTime taking into account the Status.

Thanks anyway for your help!!

[dax]

Hi dmartinezl,

If possible, could you please explain your logic to me? Then I will help you more correctly.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[az38]

@dmartinezl if your data not strictly confidential you can upload your pbix file on the cloud and share link. you can send me it via private message

do not hesitate to give a kudo to useful posts and mark solutions as solution
Linkedin