Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi PowerBI members... first post here, I'm a bit desperate. Trying to figure out how to get a value from one table based on two columns in another:
Table 1 has the make and model in different columns, table2 has all possible Models as seperate colums with relative values which I want to populate into a new column on table1. I'm hoping to get the green value/column populated in the example below
table1
Name| Make | Model | NewCol
Test | Make1 | Model3 | 789
table2
Make |Model1 |Model2 | Model3
Make1| 123 | 456 | 789
Make2| 001 | 002 | 003
Please if anybody is able to assist 🙏
Solved! Go to Solution.
Easiest Way to get Table 2 to look like Table1 is click on Table2, click on Edit Query, Select all the Model columns, Right click, Unpivot columns
DAX way will become more and more complicated if you've got a lot of models. see example
@CuriousGeorge If you want to do it DAX then please use below to unpivot the lookup table
Test96LkpUnPivot = UNION( SELECTCOLUMNS(Test96Lkp,"Make",Test96Lkp[Make],"Type","Model1","Value",Test96Lkp[Model1]) ,SELECTCOLUMNS(Test96Lkp,"Make",Test96Lkp[Make],"Type","Model2","Value",Test96Lkp[Model2]) ,SELECTCOLUMNS(Test96Lkp,"Make",Test96Lkp[Make],"Type","Model3","Value",Test96Lkp[Model3]) )
Now the table will be like this..
Then, add a new field in your main table with looking up on above table as below
LkpVal = LOOKUPVALUE(Test96LkpUnPivot[Value],Test96LkpUnPivot[Make],Test96[Make],Test96LkpUnPivot[Type],Test96[Model])
Proud to be a PBI Community Champion
Easiest Way to get Table 2 to look like Table1 is click on Table2, click on Edit Query, Select all the Model columns, Right click, Unpivot columns
DAX way will become more and more complicated if you've got a lot of models. see example
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |