cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Post Partisan
Post Partisan

How to Lookup a Value in Another Table

Hello,

 

I am trying to lookup the country in BSA00RMMC and return the Balance USD.  My Existing table I am creating the Measure in is GL001111.

How can I perform this lookup? The below code does nothing

 

Balance =
VAR RMMCBALANCE =
    MIN ( 'BSA00-RMMC'[Balance USD] )
RETURN
    IF (
        HASONEVALUE ( 'BSA00-RMMC'[COUNTRY] ),
        LOOKUPVALUE (
            'BSA00-RMMC'[Balance USD],
            'BSA00-RMMC'[COUNTRY], VALUES ( 'BSA00-RMMC'[COUNTRY] ),
            'BSA00-RMMC'[Balance USD], RMMCBALANCE
        )
    )

7 REPLIES 7
Highlighted
Super User IX
Super User IX

Re: How to Lookup a Value in Another Table

See if my blog article helps:

 

http://community.powerbi.com/t5/Community-Blog/Good-Ol-VLOOKUP-The-Ultimate-Guide-to-Lookups-in-Powe...

 


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Post Partisan
Post Partisan

Re: How to Lookup a Value in Another Table

Thanks!

 

The blog doesnt cover in depth on the Vlookup function---You do have a link to https://msdn.microsoft.com/en-us/library/gg492170.aspx

 

But I have studied that link and still cant figure out LookupValue.   I use Vlookup in Excell every day with no issue but cant grasp the dax language way of doing it.

 

Basiclly I have 2 Tables

 

Each table contains many many country records with many duplicates

 

I simply want to sum up each country Balance from each table and show the Balance from each country

 

In Excell it is very simple. In fact in my spreadsheets its not a problem doing a vlookup on a pivot table.

 

But Power BI DAX  syntax is written in 50 different ways throughout the Internet making it impossible to understand how to use it at the moment.

 

 

Highlighted
Post Partisan
Post Partisan

Re: How to Lookup a Value in Another Table

2016-11-04_13-45-51.jpg

Highlighted
Super User IX
Super User IX

Re: How to Lookup a Value in Another Table

The blog post discusses ways of doing the equivalent of VLOOKUP in Power BI. Power BI does not have a VLOOKUP DAX function so you have to do it differently.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Post Partisan
Post Partisan

Re: How to Lookup a Value in Another Table

I Understand. Yes I mislabled . 

 

Looks like I need to pass this along to a paid consultant.  Reading your article has only confused me and I still cant dfind any Solid resource showing an example.

 

Again, this should be so simple, and it is for excel and SSRS reporting. Power BI doesnt seem to handle this kind f thing very well.

 

The formula I provided worked for the same EXACT type of situation using a date field, but doesnt work on these two tables.

 

Wish Power BI had a Payment option so we could get answers from DAX experts quickly.  Maybee Ill debvelop that website and make tons of money.

 

Thanks again for attempting to help.

 

 

 

Highlighted
Microsoft
Microsoft

Re: How to Lookup a Value in Another Table

@lcasey

 

I assume you have multiple 'BSA00-RMMC'[Balance USD] for same 'BSA00-RMMC'[COUNTRY]. And you have country column with distinct values in your existing table. Right?

 

In this scenario, since you have country column in both tables, you should create the relationship between these two tables. Then you just need to create a measure like "=CALCULATE(SUM('BSA00-RMMC'[Balance USD]))", it will aggregate the 'BSA00-RMMC'[Balance USD] on country level.

 

If you don't want to create relationship, you have to create a calculated table, summarize the 'BSA00-RMMC' table with 'BSA00-RMMC'[COUNTRY] column and an aggreagated 'BSA00-RMMC'[Balance USD] column. Then you can create a calculated column with LOOKUPVALUE() to get the 'BSA00-RMMC'[Balance USD] into your existing table.

 

Regards,

Highlighted
Helper V
Helper V

Re: How to Lookup a Value in Another Table

 

"

See if my blog article helps:

 

http://community.powerbi.com/t5/Community-Blog/Good-Ol-VLOOKUP-The-Ultimate-Guide-to-Lookups-in-Powe...

 

"

 

 

thanks, an interesting article, but there is always the problem with the syntax, I must replace often the ',' with ';'

if not I have syntax errors

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors