Forum Discussion
Using different data sources to drive a date slicer
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!
3 Replies
- AnonymousNot applicable
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 relationshipRegards,
Xiaoxin Sheng
- MattAllingtonCommunity Champion
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.
- MattAllingtonCommunity Champion
Here is the article. http://exceleratorbi.com.au/relationships-power-bi-power-pivot/