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

5 Replies

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