Multiple Date Fields

[ArchStanton]

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 file here.

I have a question regarding how best to handle multiple date columns in the main fact table and my date calendar table.

The vast majority of my measures are Time Intelligence based which use the Created On system date in the fact table, this Created On system date has a many to one relationship with date.[date] field in the calendar table.

The measures based on the Created On date typically calculate how many items were open in a given time-period.

However, there are 2 other important date fields in my fact table, Resolved Date and Validation Date. The resolved date forms the basis of all closure analysis (e.g how many items closed in a month etc) but this Resolved Date is not connected to my table.

Finally, we have validation date fiield which is used much the same way as Created On date i.e, counting how many items were opened in a given time-period - the validation date also does not have a relationship with my date table.

What is the best way to handle and maintain a data model like this?

For closure analysis, I have duplicated my Date Calendar Table, renamed it Resolved Calendar Table and created a relationship between the Resolved date and this new Calendar. This has naturally added complexity to my data model but it works and is manageable for the time being, however, this is not something I want to do with the Validation date field, I do not want to create a 3rd Date calendar table.

Only the Created On date field in my main fact table has a date hierarchy, the other 2 dont. 
I obviously cannot create visuals using my main date calendar with measures based on the validation date as no relationship exists between them.

What would be the best way forward (apart from starting again and building a better data model) to manage this issue with multiple date fields?

I'm just looking for advice and best practice thats all - rebuilding the entire thing is way beyond my paygrade so I have to live with the current set-up for now.

Thanks!

[Kedar_Pande]

@ArchStanton 

Closed This Month = 
CALCULATE(
COUNTROWS(FactTable),
USERELATIONSHIP(FactTable[Resolved Date], 'Date'[Date])
)

If this answer helped, please click 👍 or Accept as Solution.
-Kedar
LinkedIn: https://www.linkedin.com/in/kedar-pande

[ArchStanton]

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.

[v-dineshya]

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

[ArchStanton]

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.

[johnt75]

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] )
)

[cengizhanarslan · Accepted solution]

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] )
)

[mdaatifraza5556]

Hi @ArchStanton 

You should use one Date table + inactive relationships.

Create relationships from that Date table to:
- Created ON -> Active

- Resolved Date -> Inactive

- Validation Date -> Inactive

 ---Use USERELATIONSHIP() inside the measures

If this answers your questions, kindly accept it as a solution and give kudos.

[DanieleUgoCopp]

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