Forum Discussion
Post PatronMultiply two tables using userelationship or Treatas
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
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
6 Replies
- amitchandak
Super User
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
Diggeramitchandak related(Table1[Rate]) not work as does not see relation as i have other relations on same table
- hashtag_pete
Helper V
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
Community Support
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
- hashtag_pete
it probably depends how many rows you have on the Conversion rates table (i.e. how many combinations must be looked through)...
