Powerbi Desktop table relationships

[Mellan3]

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! 

[JoeBarry · Accepted solution]

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