Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
78 | |
75 | |
52 |
User | Count |
---|---|
144 | |
109 | |
108 | |
88 | |
61 |