Forum Discussion

ArchStanton's avatar
ArchStanton
Power Participant
6 months ago
Solved

Multiple Date Fields

Hi, I'm looking for some help with the following: I inherited a very large and complex data model several years ago and due to the confidential nature of its content I am unable to share a pbix fi...
Need Help
  • cengizhanarslan's avatar
    cengizhanarslan
    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] )
)