Cardinality/Relationship issue across multiple tables

[Anonymous]

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?

[amitchandak]

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.

[Greg_Deckler · Accepted solution]

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]

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?

[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.

[Anonymous]

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.

[amitchandak]

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.