Add column based on two parameters

[Anonymous]

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. 

[mahoneypat · Accepted solution]

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

[Anonymous]

It worked ! Thanks.