Forum Discussion

anilkumarpoda's avatar
anilkumarpoda
Icon for Resolver I rankResolver I
3 years ago
Solved

Can we establish relationship between different date columns?

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. 

 

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

Note: Company_Code = Scheme_Code

 

Download dataset using below link.

Excel Dataset 

 

Need Help
  • anilkumarpoda's avatar
    anilkumarpoda
    3 years ago

    I got the answer from another forum. We can create a date table and map date column to date columns in other tables. But only one connection will be active. To use inactive connections in DAX we can use  USERELATIONSHIP() function.

     

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

2 Replies

  • andhiii079845's avatar
    andhiii079845
    Icon for Solution Sage rankSolution Sage
    3 years ago

    Can you please perhaps explain your probem again. I do not understand what is the problem. 
    I think that a usage is related to the membership via the REF_NUM field. (I find some REF_NUM values it in both tables columns). 

     

  • anilkumarpoda's avatar
    anilkumarpoda
    Icon for Resolver I rankResolver I
    3 years ago

    I got the answer from another forum. We can create a date table and map date column to date columns in other tables. But only one connection will be active. To use inactive connections in DAX we can use  USERELATIONSHIP() function.

     

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