Forum Discussion

ArchStanton's avatar
ArchStanton
Power Participant
5 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
    5 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] )
)

     

johnt75's avatar
johnt75
Super User
5 months ago

One option would be to create inactive relationships from your date table to the other date fields, and then create calculation groups or items to activate the appropriate relationship.

You could also activate the relationships within specific measures rather than using calculation groups, for example

Closed Cases =
CALCULATE (
    COUNTROWS ( 'Fact Table' ),
    USERELATIONSHIP ( 'Date'[Date], 'Fact Table'[Resolved Date] )
)

Validated Cases =
CALCULATE (
    COUNTROWS ( 'Fact Table' ),
    USERELATIONSHIP ( 'Date'[Date], 'Fact Table'[Validation Date] )
)