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

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
Andrewutter12
Frequent Visitor

Vlookups across two tables

Hello all,

 

I have two tables. Project table and debtors table. Both tables have matching unique transaction ID's. I'm looking to do some sort of vlookup to populate a project number on my debtors table.

 

Here's what I'm looking to achieve:

 

Project Table                                  Debtors Table

Trans ID          Project #              Trans ID   Project #

1                   20-24050               1                Blank

2                   20-24050               2                Blank

 

I want to populate a project # on my debtors table based on the matching trans ID's from each table.

 

Hope this makes sense.

 

Thanks!

1 ACCEPTED SOLUTION

@Andrewutter12 , an error arises when more than one result correspond to Debtors[Tranns ID New]. You may try this formula in a calculated column,

Column =
MAXX (
    FILTER (
        z_ISG_InFocusProjectQuery,
        z_ISG_InFocusProjectQuery[Trans ID New] = Debtors[Tranns ID New]
    ),
    z_ISG_InFocusProjectQuery[ProjectNumber]
)

 


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

View solution in original post

4 REPLIES 4
AlB
Community Champion
Community Champion

Hi @Andrewutter12 

You can do this in PQ (probably better) or in DAX. In DAX the code for the Project# column in the debtors table would be:

Project# =
LOOKUPVALUE ( ProjectT[Project#], ProjectT[TransID], DebtorsT[TransID] )

 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

SU18_powerbi_badge

Hello,

This is the error message I'm receiving when using that DAX.

Andrewutter12_0-1605296075196.png

Any thoughts?

@Andrewutter12 , an error arises when more than one result correspond to Debtors[Tranns ID New]. You may try this formula in a calculated column,

Column =
MAXX (
    FILTER (
        z_ISG_InFocusProjectQuery,
        z_ISG_InFocusProjectQuery[Trans ID New] = Debtors[Tranns ID New]
    ),
    z_ISG_InFocusProjectQuery[ProjectNumber]
)

 


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

This seemed to do the trick. Thank you so much for your help.

Helpful resources

Announcements
June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

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

May 2025 Monthly Update

Fabric Community Update - May 2025

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