Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
anilkumarpoda
Resolver I
Resolver I

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 

 

excel-model.png

1 ACCEPTED SOLUTION
anilkumarpoda
Resolver I
Resolver I

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

View solution in original post

2 REPLIES 2
anilkumarpoda
Resolver I
Resolver I

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])
    )
andhiii079845
Super User
Super User

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

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.