Forum Discussion

lefinalzugzwang's avatar
lefinalzugzwang
Frequent Visitor
9 years ago
Solved

Implementing a date table without interfering with existing relationships

Hi all,  I am trying to compare data from my Sales table, to data in my Forecasts table. I have two different date fields involved here. Forecast.Date which is the date that a particular sale from...
Need Help
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    9 years ago

    Hi lefinalzugzwang,

     

    >>But I don't fully understand how to create relationships between these dates/date table without disrupting the schema and hence other relationships.

     

    For the date table, I'd like to suggest you create with all tables date fields, for example:

     

    Date Table = 
var temp=DISTINCT(UNION(VALUES(Table1[Date]),VALUES(Table2[Date]),VALUES(Table3[Date]),...))
Return
CALENDAR(MINX(temp,[Date]),MAXX(temp,[Date]))

     

     

    Then you can create one to many relationship from calendar table to each other table which contains the date field, use calendar table as source of the filter.

     

    Regards,

    Xiaoxin Sheng

MattAllington's avatar
MattAllington
Community Champion
9 years ago

Turn off the bidirectional filtering in your current relationships. Then you can add your date table without any problems. Send me a pm with your email address and I will send you a pre release of my blog article on this topic (not due for release until 14 Feb

  • lefinalzugzwang's avatar
    lefinalzugzwang
    Frequent Visitor
    9 years ago

    Thanks a lot for this, it does sort of make sense that the relationships should be set up like this, and it's what I did. I am going to pm you once I figure out how to do so!