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.

[Sreerams26]

mine is a direct query. and the issue is still there.  From unit with Unit ID to retrieve "to Unit value". I am significantly fed-up with this related function.  

[foodd]

Hello @Sreerams26 , and thank you for reaching out to the Community Forum with your question or concern. The post you replied to is quite old and has already been marked as having a Solution. In order to ensure that your question or concern receives the attention it deserves, could you please create a new post with your question, including relevant data in either text or Excel format, and share your work in progress Power BI Desktop file. You can use any cloud storage service to share these files as part of your Forum post. If you believe it adds value, please reference the URL of this post in your new post.

If your requirement is solved, please make THIS ANSWER a SOLUTION ✔️ and help other users find the solution quickly. Please hit the LIKE 👍 button if this comment helps you.

[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!

[blodus]

So the RELATED function doesn't work? This is yet another lame workaround for something that should already work. 

I hate Power BI more and more every day becaue of stupid s#!t like this.

The workaround won't work in my case because each one is different - I'll have to add the data to the main query.