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 are missed (abandoned) and there is an agent free.

 

I tried with this code (and many others...) to create the custom column AGENT FREE  but I have no clue (and no results...)

 

 

AGENT FREE = 
var star = [StartTime (s)]
var stop = [EndTime (s)]
var cc = COUNTX('Table';FILTER('Table';'Table'[Status]="Abandoned" && star<stop<star))
return
cc

 

 

Regards,

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

13 Replies

  • az38's avatar
    az38
    Community Champion
    6 years ago

    Hi dmartinezl 

    try a measure

    = countrows(
FILTER('Table';
AND('Table'[Status]="Abandoned";'Table'[AGENT FREE?]=1)
)
)

    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

      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

  • az38's avatar
    az38
    Community Champion
    6 years ago

    dmartinezl  and all

    just for other user's information

    the issue in the data source. there are a lot of the same starttime, because there are a lot of days, one starttime (s) -to many days.

    and one more - there are a lot of rows with the same start date and time and status="Handled".

    after fix it (create DateTime column and remove duplicate with the same start date time) you could use the next measure

    AGENT FREE = if(
and(LOOKUPVALUE('Table'[EndDateTime];'Table'[StartDateTimeText];calculate(MAX('Table'[StartDateTimeText]); filter('Table';AND('Table'[Status]="Handled";'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...