Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
acao-96
Frequent Visitor

How to look up values in TB2 if a value in a column in TB1 matches with a field name in TB2

Hello,

 

I wonder if there is a way in PowerBI to look up values by searching column names to return results in the matching column.

 

For example:

TB1:

IDYear

1

Year-1
1Year-2
2Year 0
3Year-1


TB2:

IDYear 0Year-1

Year-2

1666821
2355954
3477887

 

Desired results:

IDYearQty
1Year-168
1Year-221
2Year 035
3Year-178

 

Any suggestions would be appreciated!

1 ACCEPTED SOLUTION
v-easonf-msft
Community Support
Community Support

Hi, @acao-96 

If there are not many values in the year field, you can create a calculated column in TB1 as below:

Calculated column:

Dax Qty =
SWITCH (
    Table1[Year],
    "Year 0", LOOKUPVALUE ( Table2[Year 0], Table2[ID], Table1[ID] ),
    "Year-1", LOOKUPVALUE ( Table2[Year-1], Table2[ID], Table1[ID] ),
    "Year-2", LOOKUPVALUE ( Table2[Year-2], Table2[ID], Table1[ID] )
)

veasonfmsft_0-1662369542680.png

result:

veasonfmsft_1-1662369619768.png

 

Best Regards,
Community Support Team _ Eason

 

View solution in original post

4 REPLIES 4
v-easonf-msft
Community Support
Community Support

Hi, @acao-96 

If there are not many values in the year field, you can create a calculated column in TB1 as below:

Calculated column:

Dax Qty =
SWITCH (
    Table1[Year],
    "Year 0", LOOKUPVALUE ( Table2[Year 0], Table2[ID], Table1[ID] ),
    "Year-1", LOOKUPVALUE ( Table2[Year-1], Table2[ID], Table1[ID] ),
    "Year-2", LOOKUPVALUE ( Table2[Year-2], Table2[ID], Table1[ID] )
)

veasonfmsft_0-1662369542680.png

result:

veasonfmsft_1-1662369619768.png

 

Best Regards,
Community Support Team _ Eason

 

Fowmy
Super User
Super User

@acao-96 

UnPivot feature in Power Query to convert the columns for years in the TB2 table into rows then you can simplky merge or use DAX to calculate the results.

Video: https://www.youtube.com/watch?v=Vff2kRBM95o

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

acao-96
Frequent Visitor

Thank you for your response! I was aware of the Unpivot method but I was wondering if there was a direct way to get that column in Dax instead of having to unpivot the TB2 first and then merge to get the results.

@acao-96 

Check if this could help: 
https://www.youtube.com/watch?v=9Xv8COs59tc

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.