Forum Discussion
Multiple Date Fields
- 6 months ago
In a star schema you typically do not duplicate the Date table per date role unless you must. The best-practice approach is:
-
Keep one Date dimension (DimDate)
-
Create one active relationship (usually to CreatedOn)
-
Create additional relationships to the other date columns but keep them inactive
-
Use USERELATIONSHIP() (or calc groups) to activate the right date only inside the measures
Model setup:
-
DimDate[Date] (1) → Fact[CreatedOn] (*) Active
-
DimDate[Date] (1) → Fact[ResolvedDate] (*) Inactive
-
DimDate[Date] (1) → Fact[ValidationDate] (*) Inactive
This is the standard “role-playing date” pattern in Power BI.
Then role-based versions of measures:
Items Created = COUNTROWS(Fact) -- uses active CreatedOn relationship Items Resolved = CALCULATE( [Items], USERELATIONSHIP( Fact[ResolvedDate], DimDate[Date] ) ) Items Validated = CALCULATE( [Items], USERELATIONSHIP( Fact[ValidationDate], DimDate[Date] ) )Since you said “vast majority of measures are time intelligence based”, the maintenance pain is duplicating measures per date role. For this case you could simply use calculation groups and use than as filter on page/visual depending on your requirement. Example:
CALCULATE( SELECTEDMEASURE(), USERELATIONSHIP( Fact[ResolvedDate], DimDate[Date] ) ) -
Thanks to everyone for their advice.
I did once try using USERELATIONSHIP but it was having knock-on effects with other calculations hence I duplicated the table. I think I need to re-visit what I done and try again - I could always seek advice here if something goes amiss.
Hi ArchStanton ,
Thank you for reaching out to the Microsoft Community Forum.
As you mentioned in your response, you are working on suggested solutions. could you please do let us know once you have done.
Regards,
Dinesh
- ArchStanton6 months agoPower Participant
Hi, I had already accepted one of the suggestions as a solution but it didn't save, I've just done it again and this time it has.