Datamodelling with dual date dimensions

[Takkenjong]

Hey All,

I'm trying to figure out a way to handle the following situation the smartest.

We have a datamodel containing Tickets and Plays(voorstelling).

We would like the filtering to work two ways.

- When you select a ticket you want to see which play it belongs to.

- When you select a play you want to see which tickets belong to it.

Now for the challenge:

Both of these facts contain a date.

- Tickets have the ticketselldate

- Plays have a Playdate

I'm pretty sure that this would lead to having to build two seperate time dimensions. One for plays and one for tickets.

And have a bidirectional filter between facttickets and factplays.

Is there a smarter way to handle this?

Regards,

[amitchandak · Accepted solution]

@Takkenjong , Say on report toy want filter both dates on same month/period. Then you can use a common date table and join both dates with the date of date table

In case you want to filter different periods then you need two date tables 

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos

same Table 2 dates

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in...