cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

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.

4 REPLIES 4
Employee

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.
Frequent Visitor

LOOKUPVALUE is compatible with Direct Query?

I can't use...

Employee

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

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.
Employee

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

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors