Create the relationship between tables

[twister8889]

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? 

 

[PaulDBrown]

@twister8889 

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.

[Greg_Deckler]

@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. 

[twister8889]

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 4

Thank you.

[v-xuding-msft]

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