Calculated Column in One table based on the other table in Power BI

[Anonymous]

I have below two tables with no existing relationships.

1) LoanTable 

Loan Type

Emp ID

Total Outstanding Amount

2) LoanReferenceTable

Loan Type

Interest %

Now I need to create a calcuated column in LoanTable which multiplies Total Outstanding Amount with Interest % matching the Loan Type.

Please help.

[v-kelly-msft · Accepted solution]

Hi  @Anonymous ,

Create a column similarly as below:

Column = 
var _lookupvalue=LOOKUPVALUE(LoanReferenceTable[Interest %],'LoanReferenceTable'[Loan Type],'LoanTable'[Loan Type],0)
Return
_lookupvalue*'LoanTable'[Total Outstanding Amount]

And you will see:

For the related .pbix file,pls see attahced.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[amitchandak]

@Anonymous , if they are connected 1- M

new column in LoanTable 

related(LoanReferenceTable[Interest %])

M- 1

maxx(realtedtable(LoanReferenceTable), [Interest %])

no relation

maxx(filter(LoanReferenceTable,LoanReferenceTable[Loan Type] = LoanTable [Loan Type]  ), [Interest %])

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