Forum Discussion

Mellan3's avatar
Mellan3
Frequent Visitor
3 years ago
Solved

Powerbi Desktop table relationships

Good Day, 

 

I need help with two of the tables in my powerbi. 

 

Context: 
We have 2 x tables (APBL & Syncro) containing similar data. 

These tables should each contain a Jobnumber (APBL) and an OrderNumber (Syncro) and these two numbers should match. 

Within these tables each Job / Order number should contain multiple part numbers as well as their quantities. 

 

i want to compare the data within these tabels to see if there might be a mismatch between the partnumbers & the quantities of these tables numbers. However, i am unable to make a connection between the Jobnumber (in the APBL table) and Order number (in the Syncro table)

 

I have tried to link the two tables to eachother with the model view - but non of the options seems to work as neither table contains unique keys. 

 

what i want to accomplish: 
- i want to see that in the APBL table we have job number 123456 that contains 15 parts and in the syncro table the same order number either has the same amount of parts or not. 

 

see screenshot of sample data: 

APBL:

 

Syncro: 


I would really appreciate some quidence or help with this. 

thank you! 

Need Help
  • JoeBarry's avatar
    JoeBarry
    3 years ago

    Hi Mellan3 

     

    Why don't you create a Dimension table? Maybe there is already one available on the database? If not in Power Query you could make duplicates of both tables remove all columns except the JobNumber in both tables append them together and remove duplicates

     

    Load the data into your report. Create a one to many  relationship from the DIM table to both tables on the JobNumber. If you have a DIM date table , you can create a Relationship similar to the othe rtable and you can then filter by date and create time intelligence measures also

     

    Thanks

    Joe

    If this post helps, then please Accept it as the solution 

     

1 Reply

  • JoeBarry's avatar
    JoeBarry
    Icon for Solution Sage rankSolution Sage
    3 years ago

    Hi Mellan3 

     

    Why don't you create a Dimension table? Maybe there is already one available on the database? If not in Power Query you could make duplicates of both tables remove all columns except the JobNumber in both tables append them together and remove duplicates

     

    Load the data into your report. Create a one to many  relationship from the DIM table to both tables on the JobNumber. If you have a DIM date table , you can create a Relationship similar to the othe rtable and you can then filter by date and create time intelligence measures also

     

    Thanks

    Joe

    If this post helps, then please Accept it as the solution