Date Relationships - Multiple Tables, Multiple dates.

Hi danny,

Between every two tables there is one relationship allowed to be active by default. So in your first model, all date relationships are active because there is only one relationship between every table and the date table. However, you cannot have multiple active relationships between the same two tables. So for example, if you filter the year 2021 in your date table, it logically cannot simultaneously relate that 2021 filter to both the invoice date and closed date in your invoice table because they will result in different selections.

In order to join a date table to multiple date fields within the same table you have two options:

1. create a duplicate datetable (I would recommend this option if most of your tables contain the same date fields). For example, if you have a sales table and order table and returns table, and all of them contain a "shippingDate", and "OrderDate" column, you may want to create two date dimensions calles dimOrderDate and dimShippingDate. this minimizes confusion for end users. However if you have to many unique date columns in your model you would require a lot of duplicate tables which is not recommended.
2. Alternatively, when you have a lot of different date columns within the same table, you would benefit more from the USERELATIONSHIP function. This allows you to use as many date columns as you'd like with one date dimension. The drawback is that you will need to be extra precise in your measure definitions since you would need to manually assign the relationship you'd like to use for each one.

In the case of your second model I'd opt for option 2 since I see quite a lot of date columns. By adding all the required relationships in your model view (image 1), you'll be able to use them in each measure afterwards (image 2) with the USERELATIONSHIP function.

 image 1

 image 2

Hope that helps!

Regards,

Tim