Forum Discussion
Add column based on two parameters
Hello,
I would like to add a column to the table below (which in reality has thousands or rows and over 20 columns):
Date | Machine
6/1 | EXA11
6/1 | EXA11
6/1 | EXA11
6/1 | EXA21
6/1 | EXA21
6/2 | EXA11
6/2 | EXA11
6/2 | EXA21
That column should contain a Goal based on the table below (which has a unique pair of Date-Machine):
Date | Machine | Goal
6/1 | EXA11 | 55%
6/1 | EXA21 | 67%
6/2 | EXA11 | 87%
6/2 | EXA21 | 64%
This would be the equivalent in Excel of a VLOOKUP with two parameters. How can I acheive this in PowerBI ?
Thank you.
There are at least two ways of doing this one.
1. Add a column in both tables that is the concatenate the Date and Machine, make a relationship between them, and use RELATED() to bring the Goal # to the other table (or better yet just write a measure that gets the Goal # for your calculation)
2. LOOKUPVALUE will search multiple columns. In your case, something like this in your calculated column should do it
Goal = LOOKUPVALUE(Goal[Goal], Goal[Date], Table[Date], Goal[Machine], Table[Machine], 0) // 0 is the result to return if no value found
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
2 Replies
There are at least two ways of doing this one.
1. Add a column in both tables that is the concatenate the Date and Machine, make a relationship between them, and use RELATED() to bring the Goal # to the other table (or better yet just write a measure that gets the Goal # for your calculation)
2. LOOKUPVALUE will search multiple columns. In your case, something like this in your calculated column should do it
Goal = LOOKUPVALUE(Goal[Goal], Goal[Date], Table[Date], Goal[Machine], Table[Machine], 0) // 0 is the result to return if no value found
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat