The column either doesn't exist or doesn't have a relationship to any table available

[Anonymous]

I have 2 tables connected Many to Many relationship using UniqueID. Like this.

My goal is to bring value from 1 table to another by using the following formular.

"Column = if('TaskBaselines (current)'[UniqueID]= RELATED(Assignments[UniqueID]), RELATED(Assignments[ResourceName]))"

if the Unique ID in 2 tables are equal, bring the Resource Name from Assignment table to Taskbaselines table. However, it throws this error "The column 'Assignments[ResourceName]' either doesn't exist or doesn't have a relationship to any table available in the current context.".

Unique Values are string and column i want to bring in Resource Name is string either.

What causes this issue and how could i bring 1 column from one to another table? 

Thank you so much.

[loke]

create a new calculated column in the desired  table using the below function

The following calculated column defined in the Sales table uses the LOOKUPVALUE function to return channel values from the Sales Order table.

CHANNEL = LOOKUPVALUE('Sales Order'[Channel],'Sales Order'[SalesOrderLineKey],[SalesOrderLineKey])

[PaulDBrown]

@Anonymous 

why do you need to include a new column. The best practice is to create a dimension table for common fields and use this in filter expressions, slicers, filters etc.

To achieve this, create a Dimension Table (select the otion for "New Table" in the ribbon) for the Unique [ID] fields using:

Dim Unique IDs = 

VAR TaskBaselinesIDs = DISTINCT(TaskBaselines (current) [Unique ID])

VAR AssignmentsIDs = DISTINCT(Assignements [Unique ID])

RETURN
DISTINCT ( UNION ( TaskBaselineIDs, AssignmentsIDs) )

Delete the many to many relationship and join the new Dim Unique IDs table with both the fact tables in one-to-many relationship.

Now use this new table in your visuals, measure, slicer, filters...

[Anonymous]

@PaulDBrown Based on your instructions, i could only bring the UniqueID column to new Dimension table. Trying to bring other columns by using "Related" function but it didn't work. How could i achive the common fields to new dimention table?

Also, i deleted "Many to many" relationship and create 1 to many for the new table.

Thank you

[PaulDBrown · Accepted solution]

@Anonymous 
the simplest option is to create seperate dimension tables for each of the common fields following the method I posted previously (or in Power Query). Having dimension tables in this way makes the model simpler, more efficient and easier to manage when creating visuals, slicers, filters or measures!