topic Re: IF row meets conditions then return value from a previous row. in DAX Commands and Tips

IF row meets conditions then return value from a previous row.

jl02 — Sun, 21 Feb 2021 22:34:58 GMT

Hello,

I currently have data that looks like this:

Date/Time	Agent Name	Phone Number	Running Count	Department	IsFirst	FirstDepartment
1/1/2021 3:00 PM	Agent A	1234567890	1	Department X	Department X
1/2/2021 4:00 PM	Agent B	0987654321	1	Department Y	Department Y
1/3/2021 8:00 AM	Agent C	1234567890	2	Department Y	Duplicate	*Department X*
1/10/2021 10:00 AM	Agent D	0987654321	2	Department X	Duplicate	Department Y
1/13/2021 5:00 PM	Agent E	5678901234	1	Department X	Department X

What I want to do is if "IsFirst" = Duplicate, then I want to find when that phone number first occured and return that department that first took the call. My "FirstDepartment" is the column I am trying to make. However, I have been unable to obtain those desired results.

Re: IF row meets conditions then return value from a previous row.

lbendlin — Mon, 22 Feb 2021 00:30:08 GMT

Can you guarantee that your Date/Time column has unique values and can be used for the sorting?

Re: IF row meets conditions then return value from a previous row.

jl02 — Mon, 22 Feb 2021 00:36:32 GMT

Yes, They go down to the micro-second.

Re: IF row meets conditions then return value from a previous row.

lbendlin — Mon, 22 Feb 2021 01:59:14 GMT

You don't really need the "Duplicate" check but you can add that if you want.

First Dept := var d = SELECTEDVALUE(Calls[Date/Time]) var pd = CALCULATE(max(Calls[Date/Time]),ALLEXCEPT(Calls,Calls[Phone Number]),Calls[Date/Time]<d) var pdept = CALCULATE(max(Calls[Department]),ALLEXCEPT(Calls,Calls[Phone Number]),Calls[Date/Time]=pd) return pdept