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 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's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
6 years ago

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.