Calculated column with Excchange rate calculation.

[Anonymous]

Hello. 

I have two tables: VKM1, and Currency.  

I'm trying to create calculated column in VKM to multiply Sale ( from VKM1 table ) with Rate ( from Currency table ). 

The relationships between tables are active ( many to one ), but for some reason "RELATED" function refuses to work.

How to bypass this issue, what other function can be used?

[Greg_Deckler]

@Anonymous - Try RELATEDTABLE instead.

Otherwise, not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[Anonymous]

@Greg_Deckler 

Thank you for reply. 

In details, 

Two tables. First one "Currency", like below.

Currency	Rate
USD	1,1854
JPY	125,37
BGN	1,9558
CZK	26,097
DKK	7,4508
GBP	0,90265
HUF	344,5
PLN	4,3935
RON	4,8345
SEK	10,2865
CHF	1,077
ISK	160,2
NOK	10,6585

Second one ( VKM1 ) consists of multiple rows, and columns.

Between tables there is relationship may to one ( it is active ).  

I have tried RELATEDTABLE: 

conversion = SUMX(RELATEDTABLE('Currency'),VKM1[Sale]*'Currency'[Rate])

I have tried RELATED ( even basic version inside table VKM1to check it )

test = RELATED('Currency'[Rate])

Both cases as a result I got blank column ( no errrors, no data )

Please advise.  

[Greg_Deckler]

@Anonymous - Where are you trying to create the calculated column? If it is in VKM1 table then you need to change your relationship direction to Both

[ibarrau · Accepted solution]

Hi, if creating a calculated column in VKM1 like this is not working (you only obtain blank values):

test = RELATED('Currency'[Rate])

Then check the relationship because it might be not finding matches. Add a table visualization with columns used in the relationship from both tables. Check if they are matching or you have just blank values. This is a usual problem if the columns you use in the relationship don't have the same type or have different texts cases (upper, lower)

Let us know what you find!

Regards,

[Anonymous]

@ibarrau @Greg_Deckler 

Thank you for replies, it lead me to the couse. 

Oryginal exchange rates file is in csv format, in format pasted below. After transforming in Power Query, it still had space at the beginning of each 3 letter currency code.  Removing space solved issue ... .

Date, USD, JPY, BGN, CZK, DKK, GBP, HUF, PLN, RON, SEK, CHF, ISK, NOK, HRK, RUB, TRY, AUD, BRL, CAD, CNY, HKD, IDR, ILS, INR, KRW, MXN, MYR, NZD, PHP, SGD, THB, ZAR, EUR
05 August 2020, 1.1854, 125.37, 1.9558, 26.097, 7.4508, 0.90265, 344.50, 4.3935, 4.8345, 10.2865, 1.0770, 160.20, 10.6585, 7.4683, 86.3692, 8.3311, 1.6415, 6.2311, 1.5703, 8.2277, 9.1871, 17247.57, 4.0397, 88.7090, 1405.74, 26.7125, 4.9698, 1.7809, 58.120, 1.6228, 36.759, 20.4221, 1.00