LOOKUPVALUE with multiple values and current row

[van_r]

Really stuck here on DAX syntax--could use some help.

I have a list of tasks for people to complete. Each task has multiple people assigned to it--and the work is performed in sequence (meaning person #1 completes their work before handing off to person #2). Each person has a due date for their part of the task.

In the data set I'm loading into Power BI, there are separate lines for each person assignment for each task. In the Power BI dashboard I'm creating, I can easily present all tasks, who the task is assigned to, and the date that person must complete their task. However, I want to add a measure that calculates the date that each person will "receive" the task--meaning, if person #1 is completing their part on day 5 then person #2 should expect to start working on day 5. if person #2 is supposed to be done on day 7, then person #3 should receive it by day 7.

Here is some dummy data that will help illustrate:

I need to create the "ready date" field. Each person has an "order" number--for ABC task, Mary is #2 in the queue, and her "ready" date is equal to John's "due date." In DAX, I tried to use LOOKUPVALUE as follows:

result column = "Due Date"

search1 column = "Task Title"

search1 value = "Task Title" <<< I think this is my problem, but not sure how to fix...

search2 column = "Order"

search2 value = "Order" - 1  (this finds the person in the order BEFORE the current person)

I'm getting an error "A table of multiple values was supplied where a single value was expected." I need DAX to go through every row, and for that row's Task Title, find the order number minus 1 and lookup that Due Date. In my example, when DAX gets to row 3 (Bill), it should lookup Bill's order in the process (#3) subtract 1 (so, "2"), then look up the Due Date for that same task title (ie. "ABC") for the order of 2 (which is Mary's "due date" and is therefore the date the task will be ready for Bill). Thanks in advance.

[AlB · Accepted solution]

Hi @van_r 

Your approach should work. See it in the attached file with the data provided. If you get that multiple values error that means that your table has more than one due table for a person-order combination.

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.