How to refer to many-to-many table in calculated column

[Pbiuserr]

Hello,

I have data model like pic related. I want to create a new calculated column in fact table - it suppose to be Column from WageTable1 [Co1]/ Column from WageTable2 [Col2], however I can't access WageTable1 by using RELATED() or directly. Is it because of many to many relationship? (at the moment it cannot be changed by engineers). Is there a way to perform such calculation?

Like one common thing between these tables are ID

[johnt75]

You would need to use LOOKUPVALUE specifying all the columns which would give you a unique value from WageTable1. It would also be a good idea to specify an alternate result in case LOOKUPVALUE fails to determine a single value, otherwise it could give you an error and the whole column would be marked as error.

[amitchandak · Accepted solution]

@Pbiuserr , You can try relatedTable or simple x funxtions with join

maxx(relatedtable(Table2), Table2[Value])

or

Maxx(Filter(Table2, Table2[id] = Table1[id]), Table1[Value])

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