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

[basirve]

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_Id	Rnk	Call_Result	InvalidTransfer_AgentName	Call_Disposition	Agent Name	_Created_Date_F
154736-7472	1		Gill, Carla	Transferred the Invalid	Hammerschmidt, Danielle	8/27/2024 6:50
154736-7472	2	Invalid Transfer		Invalid Transfer	Gill, Carla	8/27/2024 6:53
154736-52151	1		Davidson, Amanda	Transferred the Invalid	Ramirez, Karen	8/27/2024 14:38
154736-52151	2		Davidson, Amanda	Transferred the Invalid	Lara, Alejandro	8/27/2024 14:39
154736-52151	3	Invalid Transfer		Invalid Transfer	Davidson, Amanda	8/27/2024 14:44
154735-38773	1		Dorsey, ADRIAN	Transferred the Invalid	Horner, PaTina	8/26/2024 11:06
154735-38773	2		Dorsey, ADRIAN	Transferred the Invalid	Herdegen, Michael	8/26/2024 11:15
154735-38773	3	Invalid Transfer	Dorsey, ADRIAN	Invalid Transfer	Crosby, Kenya	8/26/2024 11:22
154735-38773	4			Invalid Transfer	Dorsey, ADRIAN	8/26/2024 11:25

Thanks & Regards,
Venkat

[rajendraongole1]

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.