cancel
Showing results for 
Search instead for 
Did you mean: 
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
Microsoft
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!

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


@mahoneypa HoosierBI on YouTube


Helpful resources

Announcements
Carousel_PBI_Wave1

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.

Power BI Summit Carousel 2

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!

BizApps LATAM 2023

Business Application LATAM Summit 2023

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

Power Platform Bootcamp

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