Displaying multiple dates in a fact table?

[pbix]

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

[v-juanli-msft · Accepted solution]

Hi @pbix 

How about changing the data model as below

for example, original main data table

Logged Date	Shipped Date	Closed Date	Created Date	Updated Date	sales
1/1/2018	1/2/2018	1/3/2018	1/4/2018	1/5/2018	1.00
1/2/2018	1/3/2018	1/4/2018	1/5/2018	1/6/2018	2.00
1/3/2018	1/4/2018	1/5/2018	1/6/2018	1/7/2018	3.00
1/4/2018	1/5/2018	1/6/2018	1/7/2018	1/8/2018	4.00
1/5/2018	1/6/2018	1/7/2018	1/8/2018	1/9/2018	5.00

after unpivoting columns

Then create a date table and connect this date table to your main data table

calendar date = CALENDARAUTO()

then on the report

Best Regards

Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.