cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Resolver I
Resolver I

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!

1 ACCEPTED SOLUTION

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



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

3 REPLIES 3

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. 



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

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



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors