Forum Discussion

CuriousGeorge's avatar
CuriousGeorge
Regular Visitor
7 years ago
Solved

Get data from second table based on two columns (formula for Custom Column added)

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 col...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    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

     

    https://ufile.io/qdmot

PattemManohar's avatar
PattemManohar
Community Champion
7 years ago

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])