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

dmartinezl's avatar
dmartinezl
Frequent Visitor
6 years ago

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's avatar
az38
Community Champion
6 years ago

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

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

     

    • az38's avatar
      az38
      Community Champion
      6 years ago

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

        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