Forum Discussion
Compare data with other rows
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
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
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
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
LinkedinHi 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 Custom1Best Regards,
Zoe ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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!!
Hi dmartinezl,
If possible, could you please explain your logic to me? Then I will help you more correctly.
Best Regards,
Zoe ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
