Forum Discussion

Andrewutter12's avatar
Andrewutter12
Frequent Visitor
5 years ago
Solved

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!

  • CNENFRNL's avatar
    CNENFRNL
    5 years ago

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

     

4 Replies

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    5 years ago

    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 

    • Andrewutter12's avatar
      Andrewutter12
      Frequent Visitor
      5 years ago

      Hello,

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

      Any thoughts?

      • CNENFRNL's avatar
        CNENFRNL
        Icon for Community Champion rankCommunity Champion
        5 years ago

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