Can we establish relationship between different date columns?

Power BI Data Model
1

Hi, in a health policy database there are 3 tables

Scheme - With scheme details along with start date.
Membership - Employe Membership details with start date.
Usage - Member ship utilization details with creation date.

Scheme start date is different from Membership start date, becuase employee can join the scheme any date after scheme start date.

Usage creation date is when a Member makes use of policy takes any medical, diagnosis or mental health services.
DATASET-XL.xlsx (348.9 KB)

In this scenario can we establish relationship between date columns? How do I proceed further in data modeling?

Note: Company_Code = Scheme_Code

2

What you really need to do is to setup a separate Dates table, and then you can create relationships between the dates in the Dates table and the dates in your other tables. Many time intelligence functions in DAX require a Dates table to function properly.

3

Hi Dave,

Even if I create a Date table, to which table date column I should map? I guess we can only make one active relationship. Kindly clarify.

Start Date for scheme
Start Date for Membership
Creation Date for Usage

4

You can create relationships between the Dates table and any of these dates that will be used in your measures. Only one can be active. The others will be inactive, but they can still be used when specifically referenced in a measure using the USERELATIONSHIP() DAX function.

For example, this is a measure from one of my reports where I have multiple date relationships:

Total RP by Start Date = 
    CALCULATE(
        [Total RP],
        USERELATIONSHIP('RP'[WorkflowStartedDate], Dates[Date])
    )
1 Like
5

Thanks Dave, I will check that.