Forum Discussion
Fact Table Multiple Dates Relationship with Dimension Date
I have a Fact Table having Multiple Date Columns keys I wanted to Show the All the Date in A Single Table While Creating a Report. How Can I show all the Dates in the Single Table as I can just have one active relationship between the Fact and Dimension Table Should I create the Role Playing Dimensions in the PBI Model Using the Available Date Column and Should I Use Another Approach for it. Looking Forward for a most effecient approach which will not put any impact on Model.
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
-
8 Replies
Hello
you should create non active relationships and then activate them accordingly with USERELATIONSHIP, see:
https://dax.guide/userelationship/when I am doing this and putting everything in a single table it is giving me wrong results
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.
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
-
Hi ShayanSidddique,
We would like to confirm if our community members answer resolves your query or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are happy to help you.
cengizhanarslan, Irwan & Stachu ,Thanks for your prompt responseThank you for your patience and look forward to hearing from you.
Best Regards,
Prashanth Are
MS Fabric community supportHi @ShayanSidddique,
We would like to confirm if our community members answer resolves your query or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are happy to help you.
Thank you for your patience and look forward to hearing from you.
Best Regards,
Prashanth Are
MS Fabric community support
