How to Lookup a Value in Another Table

[lcasey]

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
        )
    )

[Greg_Deckler]

See if my blog article helps:

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

[perpor]

"

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

[lcasey]

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.

[Greg_Deckler]

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.

[lcasey]

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.

[v-sihou-msft]

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

[lcasey]