Forum Discussion

basirve's avatar
basirve
Icon for Helper III rankHelper III
1 year ago

How to Get The Agent Name Based on certain Conditions in Power BI DAX

Dear Experts,
We want to derive the Invalid Transfer Name based on the Max Date within the Router_ID & Agent Name.
Can you please help me derive the invalid Transfer Name based on the sample data below?
I wrote using calculated Column it is working only where rank is upto 2 level , if morethan that is it failing.
Invalid_Transfer_agentName =

CALCULATE (
    MAX ( Denodo_ODS_SFDC_Case_Task[AgentNmae] ),
    FILTER (
        Denodo_ODS_SFDC_Case_Task,
        Denodo_ODS_SFDC_Case_Task[_Created_Dt_F]
            = EARLIER ( Denodo_ODS_SFDC_Case_Task[_Created_Dt_F] )
            && Denodo_ODS_SFDC_Case_Task[Cisco_Router_Id]
                = EARLIER ( Denodo_ODS_SFDC_Case_Task[Cisco_Router_Id] )
            && Denodo_ODS_SFDC_Case_Task[Rnk]
                = EARLIER ( Denodo_ODS_SFDC_Case_Task[Rnk] ) + 1
            && Denodo_ODS_SFDC_Case_Task[JobCd]
            IN { "DC11", "DCGE", "DCGG", "DCX1", "DCXB", "DCYA", "DSWP", "DSWS", "XXXX" }
                && Denodo_ODS_SFDC_Case_Task[Call_Disposition]
                IN { "Invalid Transfer", "Transferred the Invalid" }
    ),
    ALLEXCEPT (
        Denodo_ODS_SFDC_Case_Task,
        Denodo_ODS_SFDC_Case_Task[Cisco_Router_Id]
    )
)
)




 

Cisco_Router_IdRnkCall_ResultInvalidTransfer_AgentNameCall_DispositionAgent Name_Created_Date_F
154736-74721 Gill, CarlaTransferred the InvalidHammerschmidt, Danielle8/27/2024 6:50
154736-74722Invalid Transfer Invalid TransferGill, Carla8/27/2024 6:53
154736-521511 Davidson, AmandaTransferred the InvalidRamirez, Karen8/27/2024 14:38
154736-521512 Davidson, AmandaTransferred the InvalidLara, Alejandro8/27/2024 14:39
154736-521513Invalid Transfer Invalid TransferDavidson, Amanda8/27/2024 14:44
154735-387731 Dorsey, ADRIANTransferred the InvalidHorner, PaTina8/26/2024 11:06
154735-387732 Dorsey, ADRIANTransferred the InvalidHerdegen, Michael8/26/2024 11:15
154735-387733Invalid TransferDorsey, ADRIANInvalid TransferCrosby, Kenya8/26/2024 11:22
154735-387734  Invalid TransferDorsey, ADRIAN8/26/2024 11:25




Thanks & Regards,
Venkat

Need Help

1 Reply

  • rajendraongole1's avatar
    rajendraongole1
    Icon for Super User rankSuper User
    1 year ago

    Hi basirve - you can use combination of CALCULATE, RANKX, and EARLIER to calculate the InvalidTransfer_AgentName.

     

    Invalid_Transfer_AgentName =
    VAR MaxDate =
    CALCULATE(
    MAX(Denodo_ODS_SFDC_Case_Task[_Created_Date_F]),
    FILTER(
    Denodo_ODS_SFDC_Case_Task,
    Denodo_ODS_SFDC_Case_Task[Cisco_Router_Id] = EARLIER(Denodo_ODS_SFDC_Case_Task[Cisco_Router_Id])
    && Denodo_ODS_SFDC_Case_Task[Agent Name] = EARLIER(Denodo_ODS_SFDC_Case_Task[Agent Name])
    && Denodo_ODS_SFDC_Case_Task[Call_Disposition] IN { "Invalid Transfer", "Transferred the Invalid" }
    )
    )

    RETURN
    CALCULATE(
    MAX(Denodo_ODS_SFDC_Case_Task[Agent Name]),
    FILTER(
    Denodo_ODS_SFDC_Case_Task,
    Denodo_ODS_SFDC_Case_Task[_Created_Date_F] = MaxDate
    && Denodo_ODS_SFDC_Case_Task[Cisco_Router_Id] = EARLIER(Denodo_ODS_SFDC_Case_Task[Cisco_Router_Id])
    && Denodo_ODS_SFDC_Case_Task[Call_Disposition] IN { "Invalid Transfer", "Transferred the Invalid" }
    )
    )

     

    Try the above logic and let see.