Understanding Relationships

[Arcturus]

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!

[Chris99 · Accepted solution]

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

[PaulDBrown]

@Arcturus

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.

[Arcturus]

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?

[Chris99 · Accepted solution]

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.

[Chris99]

Hi @Arcturus,

It sounds like you need to find a common field between your geographic sales and product sales data and then filter by the sales date from one of the tables.

Do your sales have a unique sales id that occurs in both tables?

Chris

[Arcturus]

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!

[Chris99 · Accepted solution]

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