Forum Discussion
Fact Table Multiple Dates Relationship with Dimension Date
hello ShayanSiddiqueI
this should be done by using calendar table then use the calendar column as date in visualisation.
Thank you.
Option 1) Single Date table + USERELATIONSHIP
Keep one Date dimension and create:
-
1 active relationship (e.g., Order Date)
-
Other relationships inactive (e.g., Ship Date, Invoice Date)
Then create measures like:
Sales by Ship Date = CALCULATE( SUM(Fact[Amount]), USERELATIONSHIP(Fact[ShipDate], DimDate[Date]) )Option 2) Role-Playing Date Dimensions
If you need to:
-
Show Order Date, Ship Date, Invoice Date side by side in the same visual
-
Slice/filter independently by each date type
Then create:
-
DimDate_Order
-
DimDate_Ship
-
DimDate_Invoice
All copied from the same Date table and each one has:
-
Its own active relationship
-
hello ShayanSidddique
what do you mean by show all date in single table?
- is it getting summarize when showing the value in table visual?
in table visual, pick the option on table value then choose dont summarize.
- do you want to show all date even there is no corresponding date in your fact table?
create a calendar/date table, then create a relationship between calendar table and fact table, then use date value from calendar table instead of using date in fact table.
Hope this will help.
Thank you.
Showing all dates means that I wanted to show the dates that are connected with fk of the fact table with the dimdate date column. Using use relationship it is not giving me a way to show all dates in single table. Don't wanted to perform any aggregation on base of date just wanted to pick up the date column.
hello ShayanSiddiqueI
this should be done by using calendar table then use the calendar column as date in visualisation.
Thank you.