cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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

3,30

26/04/2017

1

3,50

26/04/2017

2

3,12

27/04/2017

1

3,80

27/04/2017

2

3,19

 

TABLE 2

ID_EXCHANGE

ID_CURRENCY

1

U

2

E

3

R

 

TABLE 3

Date Sales

Value

ID_CURRENCY

NEW COLUMN - FINAL VALUE

26/04/2017

5000

U

17.500,00

26/04/2017

4000

E

12.480,00

26/04/2017

2000

R

2.000,00

 

A) The final value must be the result of the multiplication of the VALUE by the EXCHANGE RATE on the same day of the sale for that currency.


B) IF in table 3 the ID_CURRENCY is "R", the result of the new column (FINAL VALUE) will be the same as in the VALUE column.


Can you help me, please?

4 REPLIES 4
Highlighted
Microsoft
Microsoft

Re: New column based on calculations between from same and others tables

I'm sure a DAX ninja will solve this more elegantly than I have, but I used the query editor to do a few joins to get the appropriate exchange rate based on date and currency type, then added a custom column for the conditional math.

 

Here is the M code (for tables 1 just add the code after the "Source" or "Changed Type" line, as my data was hard coded based on your example):

 

// Table1
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtE3MtU3MjA0V9JRMgRiYz0jU6VYHVQJI7CEMUzcDFWDKYY4RL2hEUzCHFWDBYY4VIOlUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Date Exchange" = _t, ID_Exchange = _t, #"Exchange Rates" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date Exchange", type date}, {"ID_Exchange", Int64.Type}, {"Exchange Rates", type number}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type",{"ID_Exchange"},Table2,{"ID_Exchange"},"NewColumn",JoinKind.LeftOuter),
    #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"ID_Currency"}, {"ID_Currency"})
in
    #"Expanded NewColumn"


// Final Value
let
    Source = Table.NestedJoin(Table3,{"Date Sales", "ID_Currency"},Table1,{"Date Exchange", "ID_Currency"},"NewColumn",JoinKind.LeftOuter),
    #"Expanded NewColumn" = Table.ExpandTableColumn(Source, "NewColumn", {"Exchange Rates"}, {"Exchange Rates"}),
    #"Added Custom" = Table.AddColumn(#"Expanded NewColumn", "Final Value", each if [ID_Currency] <> "R" then [Value]*[Exchange Rates] else [Value])
in
    #"Added Custom"

 

 

Highlighted
Microsoft
Microsoft

Re: New column based on calculations between from same and others tables

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Frequent Visitor

Re: New column based on calculations between from same and others tables

Hi @v-yulgu-msft

 

LOOKUPVALUE is compatible with Direct Query?

 

I can't use... 

 

tela powerbi.PNG

Highlighted
Microsoft
Microsoft

Re: New column based on calculations between from same and others tables

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.

 

1.PNG2.PNG

 

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

 

Regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors