Forum Discussion
Cardinality/Relationship issue across multiple tables
If it were me, I would maybe create a new table that has all of the unique codes from all of my tables and then link that to each table one-way perhaps?
Something like:
Table = DISTINCT( UNION( SELECTCOLUMNS('Table1',"__Part Number",[Part Number]), SELECTCOLUMNS('Table2',"__Part Number",[Part Number]), SELECTCOLUMNS('Table3',"__Part Number",[Part Number]), SELECTCOLUMNS('Table4',"__Part Number",[Part Number]) ) )
I created the new table (thank you!). I guess I don't reallly understand cardinality/relationships. When I try to create the relationship, the only cardinality it lets me choose is many to many but amitchandak said to avoid many to many. What Cardinality do I want and how do I select it if it isn't many to many?
Community ChampionIf you created the table with the code I provided, you should only have unique values in the table (that's the DISTINCT). So you should be able to create one to many relationships instead of many to many.
I did use your code (I think I implemented correctly) and I do end up with one column of all the part numbers
When I go to create the relationship and try to select one to many, I see the following
I have the Distinct on top -- not sure if that makes a difference or not.
- Greg_Deckler
You can try many to one instead of one to many. I would try. Otherwise, there is something funky going on with the values in that for some reason you are getting something that Power BI feels is a duplicate when creating the relationship but DISTINCT sees as a distinct value, which is odd.