Forum Discussion
Cardinality/Relationship issue across multiple tables
I have Part Numbers in four tables. Each table has the potential to have unique part numbers in it. I want to be able to have a page wide filter that seaches all four tables and displays results in various visualizations. I tried to link the Part Number fields with a many to many cardinality across all four tables. However, it will only let me make one of the relationships active. So when I search, I don't seem to get results from all 4 tables all of the time.
How do create a filter that will pull from all four 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]) ) )
7 Replies
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?
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.
These tables do not seem like a master tables. Bring or create a part table and join all four tables with that.
Try to avoid manat to many and bi-directional join unless needed by use case.
