Forum Discussion

pvgadvogados's avatar
pvgadvogados
Frequent Visitor
9 years ago

New column based on calculations between from same and others tables

I have 3 tables and I need to create a column in the last table with some conditions:   TABLE 1 Date Exchange ID_EXCHANGE Exchange Rates 25/04/2017 1 3,25 25/04/2017 2...
Need Help
v-yulgu-msft's avatar
v-yulgu-msft
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

Hi pvgadvogados,

 

You can refer to below formula to create a calculated column [Final Value] in Table3.

Final Value =
IF (
    Table3[ID_CURRENCY] = "R",
    Table3[Value],
    Table3[Value]
        * LOOKUPVALUE (
            Table1[Exchange Rates],
            Table1[ID_EXCHANGE], LOOKUPVALUE ( Table2[ID_EXCHANGE], Table2[ID_CURRENCY], Table3[ID_CURRENCY] ),
            Table1[Date Exchange], Table3[Date Sales]
        )
)

Best regards,
Yuliana Gu

pvgadvogados's avatar
pvgadvogados
Frequent Visitor
9 years ago

Hi v-yulgu-msft

 

LOOKUPVALUE is compatible with Direct Query?

 

I can't use... 

 

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    Hi pvgadvogados,

     

    Sorry, I don't know you were using Direct Query. LOOKUPVALUE is not a supported function in Direct Query mode.

     

    In order to achieve your requirement, I think you can merge tables in Query Editor as suggested by dkay84 PowerBI.

     

     

    At last, you only need to expand the merged table and remove some unnecessary columns.

     

    Regards,
    Yuliana Gu