Displaying multiple dates in a fact table?

Hi All,

Quick question about dates if anyone can help?

I have one fact table that contains 5 dates. This fact table is connected a single date table something like the below: 

Logged Date (active relationship)

Shipped Date (inactive relationship)

Closed Date (inactive relationship)

Created Date (inactive relationship)

Updated Date (inactive relationship)

The dates in the fact table are connected to the date dimension using integer keys.

This approach works great for displaying measure counts of records logged, shipped, closed etc between dates.

However, our users still want to be able to drill through to see underlying dates (e.g. 'see records'). As I am using one date dimension for 5 dates, the date dimension would just pull through the date of the active relationship.

What is the best way to make dates attached to the date dimension as inactive relationships available to the user? Should this be done as:

A measure like max date on the fact table's primary key?

A 1:1 'date dimension' that contains all dates relating to each fact?

Other?

I'd rarther not expose the underlying fact table to users so am averse to adding calculated columns into the main fact table that users can select.

Thanks!

pbix