Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi Folks,
I have 2 tables like
Loan | |||
Party name | currency | date | org_amount |
john | usd | 15th jan | 30 |
joy | usd | 16th jan | 40 |
chandler | aud | 20th jan | 20 |
monika | eur | 14th feb | 40 |
phoebe | din | 20th feb | 20 |
another table having
Currency History | ||
Date | currency | conversion amount |
20th jan | usd | 81 |
20th jan | aud | 84 |
20th jan | din | 82 |
20th jan | eur | 84 |
21st jan | usd | 87 |
21st jan | aud | 83 |
21st jan | din | 81 |
21st jan | eur | 84 |
22nd jan | usd | 92 |
22nd jan | aud | 85 |
22nd jan | din | 82 |
22nd jan | eur | 84 |
I wanted to give a date slicer as an input date from the rate history table i.e. 2nd table and want to calcualte the amount by multiplying the org_amount * conversion rate for each party. I have tried joining both the tables but i am getting huge number of rows.
whated to get an output like this. based on user input date
Loan | ||||
Party name | currency | date | org_amount | org_amount * conversion amount |
Thanks
the calculation should be base on the currency = currenct and date = date ?
Hi @Daniel29195 ,Date is from 2nd table because the values are varying and we need to chhose a date,depending upon input date it will pick up the values from the 2nd table and multiply the origional amount
ok.
but if let us say you choose 20 of january,
then all the data of the table Loan, should be multipled by the conversion amount of 20 of jan , respecting the currencies.
is that right ?
1. create a new column in both table as follow :
curreny_col_name & "|" & date
nB: make sure that the date is written in the same format in both tables.
2.now you can link the 2 tables, on this column created . ( it should be a 1 to many relationship going from currency history table (1) to loan table ( many )
3.
from here, things should be easy.
you can create a new column in the loan table as : related( curreny_history_table_name[ conversion amount col name] )
now you have the conversion amount in your loan table.
hope this helps .
If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠
tried with solutions but unable to take 2 different columns from 2 different tables
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
85 | |
83 | |
66 | |
60 | |
57 |
User | Count |
---|---|
188 | |
111 | |
105 | |
78 | |
71 |