Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago

    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])
    )
  )
Anonymous's avatar
Anonymous
Not applicable
6 years ago

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's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

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.