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] ) ) -
Hello,
I would stop duplicating calendar tables and move back to a single Date dimension, I would connect Created On, Resolved Date and Validation Date all to that same Date table, keeping Created On as the only active relationship and setting the other two as inactive.
Then I would handle everything in the measures, for closure analysis I would use CALCULATE with USERELATIONSHIP to activate the Resolved Date relationship, and for validation analysis I would do the same with Validation Date, Created On measures would continue to work normally because that relationship stays active.
Thank you
Bye