cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
van_r
Frequent Visitor

LOOKUPVALUE with multiple values and current row

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:

van_r_0-1623538643949.png

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.

 

1 ACCEPTED SOLUTION
AlB
Super User III
Super User III

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.

 

SU18_powerbi_badge

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.

 

View solution in original post

1 REPLY 1
AlB
Super User III
Super User III

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.

 

SU18_powerbi_badge

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.

 

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors