Table Relatioship when there is Primary Key

[Anonymous]

I need to compare three tables dollar amounts. They do not have primary key since they come from spreadsheets and a DB table. I need to compared at a Customer ID, PO# level, Job Card and Manufacture Level. What I have done is created a primary key on each of the tables using the four categories. It works for the most part as long as all the fields are typed exactly the same on all three tables. If I use the Customer ID only it only gives me the total of all the items, first question, is there a better way around this. I am okay with using this method.

The main question I have is when I compare the data, by creating measurements to add to my graph. It is only providing the data that matches my left table. I have tried changing the relationships but it does not seem to work. If I have switched the tables around always the table to left is the one that provided the detail.

Thanks,

Table 1
Customer ID	Manufacture#	PO#	Job#	Cost	My Manual Primary Key
N12345	ABC	P123	Job134	$ 10.00	N12345-ABC-P123-Job134
N12345	ABD	P123	Job134	$ 15.00	N12345-ABD-P123-Job134
N12345	ABT	P124	Job254	$     5.00	N12345-ABT-P124-Job254
N12345	1BT	P138	Job256	$ 25.00	N12345-1BT-P138-Job256

Table 2
Customer ID	Manufacture#	PO#	Job#	Cost	My Manual Primary Key
N12345	ABC	P123	Job134	$ 11.00	N12345-ABC-P123-Job134
N12345	ABD	P123	Job134	$ 15.00	N12345-ABD-P123-Job134
N12345	ABT	P124	Job254	$     5.00	N12345-ABT-P124-Job254
N12345	1BT2	P138	Job256	$ 23.00	N12345-1BT2-P138-Job256

Table3
Customer ID	Manufacture#	PO#	Job#	Cost	My Manual Primary Key
N12345	ABC	P123	Job134	$     10.00	N12345-ABC-P123-Job134
N12345	ABD	P123	Job134	$     14.00	N12345-ABD-P123-Job134
N12345	ABT	P124	Job254	$       5.00	N12345-ABT-P124-Job254
N12345	1BT3	P138	Job256	$     25.00	N12345-1BT3-P138-Job256

Getting this:

					Table1	Table2	Table3
My Manual Primary Key	Customer ID	Manufacture#	PO#	Job#	Cost	Cost	Cost
N12345-ABC-P123-Job134	N12345	ABC	P123	Job134	$                                 10.00	$     11.00	$           10.00
N12345-ABD-P123-Job134	N12345	ABD	P123	Job134	$                                 15.00	$     15.00	$           14.00
N12345-ABT-P124-Job254	N12345	ABT	P124	Job254	$                                   5.00	$       5.00	$             5.00
N12345-1BT-P138-Job256	N12345	1BT	P138	Job256	$                                 25.00

I am looking for something like this

My Manual Primary Key	Customer ID	Manufacture#	PO#	Job#	Cost	Cost	Cost
N12345-ABC-P123-Job134	N12345	ABC	P123	Job134	$                                 10.00	$     11.00	$           10.00
N12345-ABD-P123-Job134	N12345	ABD	P123	Job134	$                                 15.00	$     15.00	$           14.00
N12345-ABT-P124-Job254	N12345	ABT	P124	Job254	$                                   5.00	$       5.00	$             5.00
N12345-1BT-P138-Job256	N12345	1BT	P138	Job256	$                                 25.00
N12345-1BT2-P138-Job256	N12345	1BT2	P138	Job256	$                                         -	$     23.00	$               -
N12345-1BT3-P138-Job256	N12345	1BT3	P138	Job256	$                                         -		$           25.00

[rainer1]

Hi @Anonymous,

you dont need a Primary Key in Power BI because you can choose every Column you want for the relation.

I recommend you build a star schema (for further information see: https://docs.microsoft.com/en-us/power-bi/guidance/star-schema) and create a "Fact Table" to achive your needings. 

Based on your Sample Data the a good Column to start is the manufacture column this can be your "Key".

So the "Fact Table" shoud look like this

Customer ID	Manufacture#	PO#	Job#
N12345	ABC	P123	Job134
N12345	ABD	P123	Job134
N12345	ABT	P124	Job254
N12345	1BT3	P138	Job256
N12345	1BT2	P138	Job256
N12345	1BT	P138	Job256

The Relation should identified by Power BI itself but should look like this

At the end your visual is shown as you are looking for:

-------------------------------------------------------------------
Did I answer your question? Mark my post as a solution!
It was useful? Press Thumbs Up!

[Anonymous]

I am new to Power of BI how do i build a ID table, where it pulls all the data from all the tables as they will constantly change.  Also a note not sure that i can use the manufacture part#. In my sample i only used one Customer ID, I could have hundreds of different customer ID's and they could posibly use the same manufacture part #.

I am thinking that i need to append all four fields from each table to this new ID table. 

Thanks,

[PaulDBrown]

@Anonymous 

Just a question: what is the difference between each of your tables (1,2,3) since all three have the same structure? 

Depending on what the difference is (or why you need to keep them separate), you might have to keep them as separate entities or actually append them into a single table . This is a very relevant question and will determine what is the best way to set up your model.

And as @rainer1  suggests, you should create lookup or dim tables for the fields you wish to "filter" by.

To show how this is done, and since you did mention that your real data would have more than one ustomer ID for example, I have firstly taken the liberty of including a couple of extra rows (the data as provided was creating ambiguities when establishing the rleationships).

Once you have imported the tables into the model, you can reference each (right-click on each imported table), remove the columns with values ("costs")  and then append them into a single table. You can then reference this new table to create 4 new tables. In each of these 4 new tables tables keep only one of the columns (select and use the function "remove other columns") to create the Dim or lookup table for each column. Remove the duplicate rows in each of these tables.

Once you have loaded the tables to the model, establish a single to many relationship between each of your dim tables and the other fact tables (1,2 and 3) by joining the corresponding fields in each.

Your model should look like this:

Just to get a clearer view, here is the model showing just two of the 4 Dim tables linked to each fact table

Next create measures ofr the sum of each of the costs (one for each) in each fact table like this.

Cost Table 1 = SUM(Table1[Cost])

And finally create your visual using the fields from your Dim tables as rows and the measures as Values:

and here is the PBIX file for your reference:

Example of Schema structure 

[Anonymous]

Thanks for the instructions. I builded the report as you suggested and was able to recreate each of the 4 tables.  The issue i ran in to is when i tried link the tables relationship. 

I am not able to do Many to  One / Single Cross Filter Direction.  Only Many to Many and I can do both or Single crosss filter direction,  but this only allows me to do it to one of the 3 tables. Once i added to the other ones i get a error saying that "CREATE RELATIONSHIP "There are ambiguous paths between.

I did see in your example that i downloaded you were able to do Many to One relationship.  Am I missing anything.  I did had other tables in my model which I removed all the relatioships only left the model to look like your example and still not able to do single relationship.

As to your earlier question.  The difference between the three tables is that one has the initial quote cost, the other one has the cost before it was billed, and the other the final cost.   What we trying to see is the cost between the three different stages and why they might have changed.  One of the detail comparison to is the Manufacture# maybe they added additional parts after the quote or they removed parts. They could also been charge to a different PO under the same Job number.

Thanks for your help!

[PaulDBrown · Accepted solution]

@Anonymous 

You are getting the "amibguity" message when trying to create the relationships because the detail in the data generates "conflict" when setting up the independent slicers. (As I mentioned in my previous post, I actually added rows to the data you provided to avoid this in the example, based on your comment that the actual dataset would actually contain many more IDs etc...)

To see what I mean, if you look at the dataset, there is only 1 customer id and the "Manufacture#" has single corresponding PO values.

So depending on what the final dataset looks like, you can set up the model with a single lookup table in which you establish a single field as your "primary key" (though you have to be careful when removing duplicates to ensure you are not removing rows with disctinct values. As an example, if you removed duplicates from the customer id field, you would be left with a single row, which obviously doesn't reflect the nature of your data).

Sticking strictly to the dataset you provided, you could have a model set up like this (the unique values - remove duplicates - is on the "Manufacture#"):

and get this result:

Having said all that, and taking into account that the difference between the tables is a timeframe (Initial Quote, before Billing, Final Cost), I would set up the model differently. Namely, I would add a column in each of your tables to identify the quote timeframe and then append the tables into a single table.

1) add a column to each table identifying the quote timeframe:

 2) append all three tables into a single table of data:

 3) add Dim tables as in the example above + a new dim table for timeframe. The model should look like this:

 4) create a mesaure for the sum of Costs:

Cost = SUM('Data Table'[Cost])

5) And finally set up your matrix using the fields from the Dim tables (notice the "Quote Timeframe" in th column bucket)

And here is the PBIX file for your reference:

PBIX file Append Tables 

Hope that helps!

[Anonymous]

Thanks for the new input.  I was able to resolve the Many to one relationship, by formating each of the column to Upper Case.  I am not sure if this is a PBI issue.  i had previously had Trim and Clean the data prior to removing duplicates and it only worked for one of the tables.  After i changed the format to all be upper case. It allow me to do Many to one relationship.

I will try your new format as well.

Thanks again!

[PaulDBrown]

@Anonymous 

Thanks for getting back. 
That's weird: once in the model, PBI is theoretically "case"-agnostic I believe (not so Power Query).

ref: From Curbal: case sensitivity