Need help DAX LOOKUP formula which would work like VLOOKUP

[coolshib]

Hello Everyone,

I have two tables

Table 1:-

Voucher No.	Invoice No.	Invoice Amount	Invoice Date	Vendor Name
1	ABC-1	100	14/05/2019	ABC Company
1	ABC-2	200	14/05/2019	ABC Company
2	EFG-1	300	31/05/2019	EFG Company
2	EFG-2	400	31/05/2019	EFG Company
3	XYZ-1	500	30/06/2019	XYZ Company
3	XYZ-2	600	30/06/2019	XYZ Company

Table No.2:-

Invoice No

ABC-1

ABC-2

EFG-1

EFG-2

XYZ-1

XYZ-2

Now i am looking for a DAX formula in table no.2 which would return the details like vendor name, Invoice Date, Voucher No. etc.

I dont want any power query solution because that would increase my memory. I am looking for need DAX measures which would return the results using the relationship between two tables.

I hope i explained my query properly.

Thank you so much advance.

Best Regards,

Shib

[harshnathani · Accepted solution]

Hi @coolshib ,

Try this measure

Vendor Invoice Date Related = CALCULATE(LASTNONBLANK('Table No1'[Invoice Date],1), FILTER('Table No2','Table No2'[Invoice No] = MAX('Table No1'[Invoice No.])))

Vendor Name Related = CALCULATE(LASTNONBLANK('Table No1'[Vendor Name],1), FILTER('Table No2','Table No2'[Invoice No] = MAX('Table No1'[Invoice No.])))

Incase you are looking for a Calculated Column

Column = RELATED('Table No1'[Vendor Name])

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[coolshib]

Thank you so much @harshnathani ...

The DAX measures give me the results which i eaxctly wanted.

Many thanks.

Best Regards,

[Greg_Deckler]

@coolshib - If you just create a relationship between the tables then you can display all of the information in a visual, there is no need to write DAX or create calculated columns.

[mahoneypat]

If Table 1 is on the 1 side of a relationship on Invoice No, you can just use the RELATED function

VendorName = RELATED(Table1[Vendor Name])

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat