Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
pbix
Helper III
Helper III

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

 

1 ACCEPTED SOLUTION
v-juanli-msft
Community Support
Community Support

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

5.png

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

calendar date = CALENDARAUTO()

then on the report

3.png4.png

 

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.

View solution in original post

1 REPLY 1
v-juanli-msft
Community Support
Community Support

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

5.png

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

calendar date = CALENDARAUTO()

then on the report

3.png4.png

 

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.