Connecting to Date Dimension when data is by Year

[kressb]

I use a DateDimension and a Key to harness all the different tables/data in my builds. 

Sometimes I have a data table where the information is for the year instead of individual dates, ex: a budget

UniqueID	Site	Number1	CalendarYear
1	North	$30,000	2021
2	South	$15,000	2021
3	East	$47,000	2021

Whenever I try to link this to the DateDimension, I'll get an inactive Many to One relationship.

What is the proper way to treat data like this so that you can get active relationships?

If there is a link someone would be willing to provide I will go and read and figure it out. I just can't find the answer.

[Jihwan_Kim · Accepted solution]

In my opinion, there are three ways to solve this.

There are some pros and cons, but without knowing your whole data model, it is hard to say which one is the best.

1. MMR: many to many relationship (filter direction is from dates table to budget table) -> only the case your visualizations are filtered by year level, otherwise some of the measures will generate unreasonable numbers.

2. create a bridge table (year table), and create a physical relationship between yeartable-datestable & yeartable-budgettable

2. use TREATAS function in a measure to create a virtual relationship