Need to Fetch one column data from one table to another table which have many to many relationship.

[Anand2]

I have 2 tables loaded in power Bi named "cube table" and the "all pid" table. so now in the cube table i have project number and in the all pid table i have oracle pid which are related by many to many relationships. so now by relating or matching the these 2 columns i need to load the sales opportunity column present in the All pid table to the cube table as the opportunity Id column, I tried diffrent ways using Lookupvalue, related, merge queries using query editor etc etc but I am getting an error.

"Multiple column values were supplied where single value was expected"

So any one can help me with this scenario.

[Anonymous]

Hi @Anand2 

As @tharunkumarRTK  mentioned ,you can consider to use brige table.

1.You can summerize the  project number to new table, then create relationship aomong the tables.

e.g 

Table =
SUMMARIZE ( 'Table', [project number] )

2.Crete relationship among the tables.

e.g Table[project number]->cube table[project number] (one to many)

Tale[project number]-> all pid [oracle pid ](one to many)

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[tharunkumarRTK]

@Anand2 

many to many relationship is not a good pratice. I would suggest you to create a bridge table https://learn.microsoft.com/en-us/power-bi/guidance/relationships-many-to-many#relate-many-to-many-d... to avoid such relationships. 

Then you will be able to use related dax function to retrieve one side value to many side and relatedtable for vise versa.

If the post helps please give a thumbs up

If it solves your issue, please accept it as the solution to help the other members find it more quickly.

Tharun