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]) ) )
Community ChampionIf 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?
- amitchandak
Super User
Refer this doc:https://docs.microsoft.com/en-us/power-bi/guidance/
Star schema is preferred choice. Means we should have set of dimesnions(master) and set of facts(Transaction table). Ideally Dimesnion should join to fact with 1 to many join. Like Date , product, geography are dimension and Sales , purchase are facts.
Now when join sales to purchase for item it will be many to many. That should be avoided. But yes in real world there are many place where we have to deal with many to many relation.
- Greg_Deckler
If 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.
