Using different data sources to drive a date slicer

[shelbsassy]

I have looked all over and haven't found anything to help me with this.

What I am trying to do is create a slicer to drive the dashboard based on the claims date.

I have one table with the service date of the claims (1/1-1/31)

I have another table with the premium for each month (1/1, 2/1)

I have another table for month which is the first date of each month in the year.

I keep getting an error trying to create a relationship between the tables in any direction saying I can't create the relationship because the values in one of the columns has to be unique.  I don't understand, the Month table which has 1/1/2017 2/1/2017 and month nums of 1 and 2 are unique.  I created a column in each of the other tables for the month num to try and create a relationship that way but I am stil getting the relationship error.  Why can't I create a relationship between these tables?  Basically I want to have the dashboard be dynamic across months so I was trying to roll the claims for the month of January to 1/1 or month num 1 but that's not working.  Does anyone have any other ideas?  Each month has a premium that changes and the premium is in the Members table assigned with 1/1/2017, 2/1/2017, etc.

Thanks so much!

[MattAllington]

I just wrote a blog article that will help you but it won't be released until 14 Feb. If you send me a PM I will send you an early release copy. 

[MattAllington · Accepted solution]

Here is the article. http://exceleratorbi.com.au/relationships-power-bi-power-pivot/

[v-shex-msft]

Hi @shelbsassy,

I'd like to suggest you create a CALENDAR table and create the relationships between CALENDAR and each tables which you mentioned.
Add a calculated column to calendar table to store the month number, then you can use the month number to filter all of the tables which contains the relationship

Regards,

Xiaoxin Sheng