Forum Discussion

shelbsassy's avatar
shelbsassy
Resolver I
9 years ago
Solved

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

  • Anonymous's avatar
    Anonymous
    Not 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 relationship

     

    Regards,

    Xiaoxin Sheng

  • MattAllington's avatar
    MattAllington
    Community 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.