compare contact per agent

Question

Hi&nbsp;here is my problem ; we create a KPI in our call center - For any agent answering call we want to do the following:Agent A in a Month (let's call it month 1) answered let's say 10 call - from 1 to 10, identified by their phone number.When come month 2, we want to see if any of these phone number from Month 1 called back again in Month 2.&nbsp;Agent A in January || FebruaryTFN1 : call in January|| did not call back in February = result = 0TFN2&nbsp; call in January || did call back || then result = 1TFN3 call in January || did call back&nbsp; || then result = 1...The final result would be that 2 out of 3 caller in Month 1 have called again in the following monthHow can I do this with hundred of agents ? (dax or BI / Query is okay !)Thank you for your timeVincent&nbsp;&nbsp;

danextian · Answer

Hi Sopall4424 ,
&nbsp;
This is better off done in DAX as this involves scanning a table. Below is a sample formula and assumes that calculation is for the first and second months only - following months are to be ignored.
Second Month Caller by Agent = 
VAR _FirstDate =
    CALCULATE ( MIN ( Data[Date] ), ALLEXCEPT ( Data, Data[Agent], Data[Caller] ) ) // when was the customer first called the agent?
VAR _SecondDate =
    EDATE ( _FirstDate, 1 )
VAR _FirstPeriod =
    FORMAT ( _FirstDate, "YYYYMM" )
VAR _SecondPeriod =
    FORMAT ( _SecondDate, "YYYYMM" ) //when is the expected second month the customer should be calling again
RETURN
//return 1 only if it is the second month and the customer has called within that month
    IF (
        _SecondPeriod = Data[Period],
        CALCULATE (
            COUNTROWS ( Data ),
            FILTER (
                FILTER (
                    FILTER ( ALL ( Data ), Data[Agent] = EARLIER ( Data[Agent] ) ),
                    Data[Caller] = EARLIER ( Data[Caller] )
                ),
                Data[Period] = _SecondPeriod
            )
        )
    )

Please see attached pbix for your reference.
&nbsp;

sopall4424 · Answer

Thank you&nbsp; for this response, I really appreciate it. Food for thought.I know the hardest part is to clearly express your problem clearly. the solution is close to what I need but not exactly that.Let's say agent A took 10 calls (call 1 .... call 10) in January. What I need to measure is not that in any of the call answered in January will call him back but just if any of the callers will call back again the next month ( I do not need the couple agent-call for the following month), I just want to check if the customer call back, not matter who he talked to, over the next 2 months after the first call.Looking at your solution, I suppose that the solution is in the filter.. but not good enough to with dax to update your solution..Vincent.&nbsp;&nbsp;

danextian · Answer

Hi&nbsp;Sopall4424&nbsp;,
&nbsp;
Try the following. Notice that I remove the parts that involved scanning the agent.
&nbsp;
Second Month Caller = 
VAR _FirstDate =
    CALCULATE ( MIN ( Data[Date] ), ALLEXCEPT ( Data, Data[Caller] ) ) // when did the customer first called?
VAR _SecondDate =
    EDATE ( _FirstDate, 1 )
VAR _FirstPeriod =
    FORMAT ( _FirstDate, "YYYYMM" )
VAR _SecondPeriod =
    FORMAT ( _SecondDate, "YYYYMM" ) //when is the expected second month the customer should be calling again
RETURN
    //return 1 only if it is the second month and the customer has called within that month
    IF (
        _SecondPeriod = Data[Period],
        CALCULATE (
            COUNTROWS ( Data ),
            FILTER (
                FILTER ( ALL ( Data ), Data[Caller] = EARLIER ( Data[Caller] ) ),
                Data[Period] = _SecondPeriod
            )
        )
    )

&nbsp;
&nbsp;

sopall4424 · Answer

Thank you&nbsp;danextian ! This is really helpful -- I'm not exactly where I need to be but I do see that with DAX formula, I can achieve my goal. It seems like this thing comes easy for you !Let me ask you this then :&nbsp;Let's say an agent A anwer the following call in Januaryphone number 1phone number 2phone number 3phone number 4&nbsp;Month + 1 (FebruaryI need to identify if any of the caller called back ( at the point the agent does not matter, have they called back if all that matter)Let's say&nbsp;phone number 2phone number 3have called back&nbsp;I would need to identify that these 2 calls back in February&nbsp;Then in March, only&nbsp;phone number 3 call back.I would need to identify that 1 call back in March&nbsp;Thank youvincent

danextian · Answer

You will need another column to identify whether a caller calls in both the second and third month.
&nbsp;
Second and Third Month Caller = 
VAR _FirstDate =
    CALCULATE ( MIN ( Data[Date] ), ALLEXCEPT ( Data, Data[Caller] ) ) // when did the customer first called?
VAR _SecondDate =
    EDATE ( _FirstDate, 1 )
VAR _ThirdDate =
    EDATE ( _FirstDate, 2 )
VAR _FirstPeriod =
    FORMAT ( _FirstDate, "YYYYMM" )
VAR _SecondPeriod =
    FORMAT ( _SecondDate, "YYYYMM" ) //when is the expected second month the customer should be calling again
VAR _ThirdPeriod =
    FORMAT ( _ThirdDate, "YYYYMM" ) //when is the expected second month the customer should be calling again
VAR __SecondMonthCaller =
    CALCULATE (
        COUNTROWS ( Data ),
        ALLEXCEPT ( Data, Data[Caller] ),
        Data[Second Month Caller] = 1
    )
RETURN
    //return 1 only if it is the third month and the customer has called within that and the second month    
    IF (
        _ThirdPeriod = Data[Period], //YOU CAN CHANGE THIS TO "_ThirdPeriod = Data[Period] || _SecondPeriod = Data[Period] " IF  YOU WANT TO SHOW THE COUNT IN BOTH MONTHS
        CALCULATE (
            COUNTROWS ( Data ),
            FILTER (
                FILTER ( ALL ( Data ), Data[Caller] = EARLIER ( Data[Caller] ) ),
                Data[Period] = _SecondPeriod
            )
        )
    )

Forum Discussion

compare contact per agent

6 Replies

Related content

Fabric Data Agents - residency

Share Fabric Data Agent

Fabric AI Agents and Copilot

Fabric data agent

Copilot Agent Usage Report