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

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

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...