cancel
Showing results for
Did you mean:
Helper III

## Need help DAX LOOKUP formula which would work like VLOOKUP

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.

Best Regards,

Shib

1 ACCEPTED SOLUTION
Community Champion

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)

4 REPLIES 4
Community Champion

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)

Helper III

Thank you so much @harshnathani ...

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

Many thanks.

Best Regards,

Super User

@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.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Microsoft

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

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

Announcements

#### 2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

#### Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

#### Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

#### Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors