Forum Discussion

dmartinezl's avatar
dmartinezl
Frequent Visitor
6 years ago
Solved

Compare data with other rows

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 a...
Need Help
  • az38's avatar
    az38
    6 years ago

    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

dax's avatar
dax
Community Support
6 years ago

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's avatar
dmartinezl
Frequent Visitor
6 years ago

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's avatar
    dax
    Community Support
    6 years ago

    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.