Forum Discussion

lcasey's avatar
lcasey
Post Prodigy
9 years ago
Solved

Need assistance with LookupValues

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?  
Need Help
  • Vvelarde's avatar
    Vvelarde
    9 years ago

    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] ) )
)
Vvelarde's avatar
Vvelarde
Community Champion
9 years ago

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's avatar
    lcasey
    Post Prodigy
    9 years ago

    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's avatar
      Vvelarde
      Community Champion
      9 years ago

      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's avatar
        lcasey
        Post Prodigy
        9 years ago

        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.