Forum Discussion
Create the relationship between tables
Hi guys,
I would like to create a relationship between some tables with common fields (Department, Team, Month) these are my filters. Currently, my relationship is many to many because I have duplicates values by all tables (it's duplicate because the table was built by multiples identical files separated each one by clients). In this model, I have problems, when I select the Department from Table5 only the chart/table by Table6 are affecteds.
What is the best way to solve this model?
-Create one only table with Month, Department, Team, and a primary key Month&Deparment&Team (table7)?
-All tables will have the primary key Month&Department&Team to link with table7?
-The calendar table will be linked with Month in table7?
Obs: If it's correct to create a unique key Month&Department&Team, it's possible to create a serial number to this combination?
4 Replies
twister8889 - So, couple ways to do this. Not sure where your many-to-many is though or what is causing it in the data. But yes, combo keys are a good, tried and true method but I always build them like this:
Key = [Column1] & "-" & [Column2] & "-" & [Column3]I put the separators in to avoid clashes like:
9 999 matching 99 99 for example.
First of all, thank you for your answer...
-OK, I will create this key, but It's possible to transform the key to number by these three columns? (Month Department Team) as 1?
-Its better use concatenate & "-" or can I use Merge Columns?
About the many-to-many, I will validate but is between table 1 and table 4Thank you.
Hi twister8889 ,
There are a few documents about data model. You could reference them to optimize your model.
Understand star schema and the importance for Power BI
Many-to-many relationship guidance
Bi-directional relationship guidance
Relationship troubleshooting guidance
1) you should create a Calendar Table covering the full range of dates included in your fact tables: create a one-to-many relationship with all your date fields in the fact tables. If any of your fact tables has a different granularity (month vs date), create a bridge table using a YYYYMM key and link this bridge table with the calendar table and the affected fact tables, again, with a one-to many relationship.
2) create dimension tables for all common fields (at the lowest hierarchy) in your fact tables with unique values and create one-to-many relationships with your fact tables (do not include fields related to calendar fields.).
3) Avoid at all costs many-to-many relationships. (They cause havoc)
if you can provide a sample dataset we can build a mockup to help you.
