Need assistance with LookupValues

[lcasey]

Hello,

I have two tables, each tble contains country and amount.

How can I use the LookupValue function in Power BI to return the balanc of the country from anoyther table?

[Vvelarde]

hi @lcasey

the easiest way in your case is:

1. create a new table with distinct countries

New Table

Countries=Distinct('Table1'[Country])

2. Related with both tables

3. in a table visual put countries table column (Country) and amountsfrom Table 1 and Table 2.

[lcasey]

I was hoping to avoid any additional tables as the Countries would then need to be seperatly managed in another table.

How come this DAX formula work on another two tables that are unrelated?

EXCHRATETOUSE =
VAR EXCHDATETOUSE =
    MIN ( 'BSA00-MC'[EXCHDATE] )
RETURN
    IF (
        HASONEVALUE ( 'BSA00-RMMC'[CUSTNMBR] ),
        LOOKUPVALUE (
            'BSA00-MC'[XCHGRATE],
            'BSA00-MC'[CURNCYID], VALUES ( 'BSA00-RMMC'[CCURNCYID] ),
            'BSA00-MC'[EXCHDATE], EXCHDATETOUSE
        )

I am basically trying to do the same exct thing, but return balances instead of Exchange rates

[Vvelarde · Accepted solution]

@lcasey

Another Way using your tables sample:

ValuesFromTable2 =
IF (
    HASONEVALUE ( Table1[Country] ),
    AVERAGEX (
        SUMMARIZE (
            Table2,
            Table2[Country],
            "Values", CALCULATE (
                SUM ( Table2[Values] ),
                FILTER ( Table2, Table2[Country] = VALUES ( Table1[Country] ) )
            )
        ),
        [Values]
    ),
    CALCULATE ( SUM ( Table2[Values] ) )
)

[lcasey]

Hmmm---Both Options work, I just cant get numbers to calculate correctly.

Ill need to send this to you offline,   This report is a huge pain. I developed it easily in SSRS , but Power BI makes simple things soo complicated. Maybee its the language as SQL is extreemly easy to understand, I find thi language almost impossible to understand.

Thanks!!! and Ill send you details soon on this report. It is a Very complex report and requires many calculations.  Hopefully it wont be that bad since you are an expert at DAX.

[v-caliao-msft]

Hi @lcasey,

Lookupvalue syntax
LOOKUPVALUE( <result_columnName>, <search_columnName>, <search_value>[, <search_columnName>, <search_value>]…)

So you can use the DAX below to create a calculated column.

Column = LOOKUPVALUE(Table1[Amount],Table1[Country],Table2[Country])

Regards,

Charlie Liao