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!
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!
Community ChampionAnonymous
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!
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).