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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
coolshib
Helper III
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 AmountInvoice DateVendor Name
1ABC-110014/05/2019ABC Company
1ABC-220014/05/2019ABC Company
2EFG-130031/05/2019EFG Company
2EFG-240031/05/2019EFG Company
3XYZ-150030/06/2019XYZ Company
3XYZ-260030/06/2019XYZ 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

 

 

 

 

 

1 ACCEPTED SOLUTION
harshnathani
Community Champion
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.])))

 

 

 

1.jpg

 

 

 

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)

View solution in original post

4 REPLIES 4
harshnathani
Community Champion
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.])))

 

 

 

1.jpg

 

 

 

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)

Thank you so much @harshnathani ...

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

Many thanks.

Best Regards,

Greg_Deckler
Super User
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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
mahoneypat
Employee
Employee

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!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors