Forum Discussion
Table Relatioship when there is Primary Key
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:
Hope that helps!
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!
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,
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:
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!