Multiply two tables using userelationship or Treatas

[Digger]

I have to tables

Table1

CurrencyRates

Name | Rate | Date | Custom 

USD | 1,35 | 2021-08-01 | USD2021-08-01

AFN | 0,35 | 2021-08-01 | AFN2021-08-01

BRL| 0,05 | 2021-08-01 | BRL2021-08-01

Table2

ID | Date | Amount | Currency | 

1 | 2021-08-01 | 89 | USD | USD2021-08-01

2 | 2021-08-01 | 8059 | AFN | AFN2021-08-01

Need add custom column to table2, PriceinEur = Calculate(DIVIDE(Table2[Amount],Table1[Rate]), USERELATIONSHIP(Table2[Custom],Table1[Custom]))

But it not work and return wrong sums

Also i cant merge tables in PQ as got formula firewall error, as Table1 goin from one source Table2 from another

[hashtag_pete]

it probably depends how many rows you have on the Conversion rates table (i.e. how many combinations must be looked through)... 

[hashtag_pete]

Hello Digger, 

have you tried lookupvalue? Something like

Conversion = DIVIDE ( Table2[Amount] , LOOKUPVALUE(Table1[Rate], Table1[Custom], Table2[Custom]))

Does that help? If so, please

• mark this post as a solution
• give kudos

Thanks hashtag_pete

[Digger]

@hashtag_pete  does lookup on 10 mil rows is good idea?

[v-easonf-msft · Accepted solution]

Hi,  @Digger 

You can take a try the measure  as below:

Measure_PriceinEur = Calculate(DIVIDE(max(Table2[Amount]),MAX(Table1[Rate])), USERELATIONSHIP(Table2[Custom],Table1[Custom]))

Then apply it to a table visualization.

Best Regards,
Community Support Team _ Eason

[amitchandak]

@Digger , using the custom column if you can create 1-M from relation from Table 1 to table 2 then try

Sumx(Table2, DIVIDE(Table2[Amount],related(Table1[Rate])) )

Or add new column in table 2

Rate= related(Table1[Rate])

if needed,

refer 4 ways to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8

[Digger]

@amitchandak   related(Table1[Rate]) not work as does not see relation as i have other relations on same table