topic Re: Vlookups across two tables in DAX Commands and Tips

Vlookups across two tables

Andrewutter12 — Fri, 13 Nov 2020 17:20:48 GMT

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!

Re: Vlookups across two tables

AlB — Fri, 13 Nov 2020 18:27:46 GMT

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

Re: Vlookups across two tables

Andrewutter12 — Fri, 13 Nov 2020 19:34:50 GMT

Hello,

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

Any thoughts?

Re: Vlookups across two tables

CNENFRNL — Sat, 14 Nov 2020 00:49:47 GMT

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

Re: Vlookups across two tables

Andrewutter12 — Sun, 15 Nov 2020 20:14:27 GMT

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