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

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

      • az38's avatar
        az38
        Community Champion
        6 years ago

        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