Forum Discussion
Understanding Relationships
Hello everyone,
I am having a hard time understanding how relationship work, what they do, etc
My situation is as follows: Say I have a query containing sales data at the geographic level, and another one at the product level. Both contain the exact same date rows.
My key question is: What kind of relationship should I create to have a dashboard in which selecting a specific date filter changes the visualization content for both the product and geographic level?
What made sense spontanously was to create a relationship from date of query 1 with date of query 2, but that's not the way it works. Also, I'm not sure I understand the difference between many to one, one to many, both and single, etc.
Thanks in advance!
Hi Arcturus
My first thought would be to go for
- Sales table that contains a unique id, a sales date, an amount, a product id, and a location id,
- Product table that contains a product id and a description
- Location table that contains a location id and a description
Then create the following relations
- Sales product id to Product product id (Many to one)
- Sales location id to Location location id (Many to one)
That should get you going, and then later you can flesh it out, maybe by adding a region to your Location table, or adding a new Salesperson table and a salesperson id to the Sales table.
Hope this helps,
Chris
If you want a date table then you should have a single row per date. The error suggests that the date table has duplicate dates.
6 Replies
If I'm understanding your predicament correctly, just create a date table and relate the date to each of the dates in the other two fact tables. That takes care of the date filtering.
You can also create lookup tables for geography and for product (and relate them to each corresponding fact table), and use theses to filter in slicers, matrices and visuals in general.
Thanks PaulDBrown,
I might very well be doing it wrong, though when I try to create a relationship between my Date table (which only comprises a Date column) and the Date columns from my other Queries, I get the following error message:
You can't create a relationship between these two columns because one of the columns must have unique values.
What am I missing?
If you want a date table then you should have a single row per date. The error suggests that the date table has duplicate dates.
Thanks for offering help Chris99, much appreciated!
Currently there is none (I am training on fictious data sets that I have created).
Could it be done w/o one? Maybe by connecting the dates, which are 100% identical in each query and occur in both.
If not, could you please confirm my understanding of the way it would work:
- I would need to create an extra identical column in each query table, containing Sales ID.
- I would need to create a relationship linking the Sales ID from the geographical query, to the Sales ID of the sales query (Not sure this is right, as I recall not being able to create relationships between identical fields)
- I would need to set the relationship to be a one to one, as each Sale ID will be unique
- I would need to set the relationship to be both cross-filtering as there is information from both table to be used in the visualisations.
Thanks again!
Hi Arcturus
My first thought would be to go for
- Sales table that contains a unique id, a sales date, an amount, a product id, and a location id,
- Product table that contains a product id and a description
- Location table that contains a location id and a description
Then create the following relations
- Sales product id to Product product id (Many to one)
- Sales location id to Location location id (Many to one)
That should get you going, and then later you can flesh it out, maybe by adding a region to your Location table, or adding a new Salesperson table and a salesperson id to the Sales table.
Hope this helps,
Chris
