1 to many for Year and Data Dimension

[svc-dashboard]

Take these three tables:

(1) Table 1 — Data Dimension Table

Date	Year	Month
01/01/2020	2020	Jan
02/01/2020	2020	Jan
03/01/2020	2020	Jan
…	…	…

(From 01/01/2020 to 31/12/2021)

(2) Table 2 — Daily Data Table

Date	Customer	Amount
02/01/2020	P	3
04/01/2020	S	4
…	…	…

(3) Table 3 — Yearly Data Table

Year	Type	Amount
2020	Budget	100
2020	Actual	90
2021	Budget	110
2021	Actual	105

+++

If we want to join Table 1 and Table 2 — we have a lovely 1 to many join!

If however, we want to join Table 1 and Table 3 — we have many to many join.

Q: Is a many-to-many join bad?

Q: Is there a more optimal way to set this up to avoid the many-to-many join?

e.g. Should I create a Year Dimension Table? This could be joined to Data Dimension (1:*) and Table 3 (1:*)

[Anonymous · Accepted solution]

Hi @svc-dashboard ,

1# No, the relationship depends on your requirement. Perhaps some document could help you understand.

https://docs.microsoft.com/en-us/power-bi/guidance/relationships-many-to-many 

https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-many-to-many-relationships 

2# I'm afraid not. In your data model, table 2 and table 3 also have many-to-many relationship if you add a year column.

Best regards,

Jay

[amitchandak]

@svc-dashboard , You can create a date from year and join it with date tbale

date =  date([Year],1,1)

also refer

Distributing/Allocating the Yearly Target(Convert to Daily Target): Measure ( Daily/YTD): Magic of CLOSINGBALANCEYEAR With TOTALYTD/DATESYTD: https://community.powerbi.com/t5/Community-Blog/Power-BI-Distributing-Allocating-the-Yearly-Target-C...

Distributing/Allocating the Yearly Target(Convert to Daily Target): https://community.powerbi.com/t5/Community-Blog/Distributing-Allocating-the-Yearly-Target-Convert-to...