Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
it probably depends how many rows you have on the Conversion rates table (i.e. how many combinations must be looked through)...
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
Thanks hashtag_pete
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
@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
@amitchandak related(Table1[Rate]) not work as does not see relation as i have other relations on same table
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |