Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
It worked ! Thanks.
User | Count |
---|---|
107 | |
89 | |
81 | |
76 | |
73 |
User | Count |
---|---|
112 | |
104 | |
96 | |
74 | |
66 |