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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Mann
Resolver III
Resolver III

Relationship between table with duration ranges

Hi Guys,

 

I have two tables on which I want to create relationship. Here are the details:

One table is a fact table with Individual IDs and Date as two of the fields in it. Other table is master HR table with Individual IDs, From Date and To Date in it. I need to create a relationship between them. I have shown some sample data for this problem.

 

Fact Table:

Ind IDDate
A2/1/2020
A2/12/2020
B2/3/2020
C2/4/2020
D2/5/2020
B2/6/2020
C2/17/2020

 

HR Table

Ind IDFrom DateTo DateAttribute1
A1/1/20202/3/2020abc
A2/3/20202/14/2020def
B2/1/20202/5/2020ghf
C1/4/20202/7/2020dvd
D2/1/20202/6/2020bee
B2/6/20202/10/2020vfd
C2/7/20202/19/2020fnm

 

I need to get Attribute in Fact Table using relationship only (I have many other attirbutes to be pulled). I was using this calculation before but getting performance issues now:

 

Attribute1 =
CALCULATE (
    MIN ( HR[Attribute1] ),
    FILTER (
        HR,
        HR[Ind ID] = Fact[Ind ID]
            && HR[From Date] <= Fact[Date]
            && HR[To Date] >= Fact[Date]
    )
)

 

  

How can I create relationships between these two tables?

I did this so far: Created an Index column in HR Table and then created calculated column in Fact using the above calculation to pull that "Index" column in Fact table. When I try to create a relationship between these two Index columns, I am running into error saying "A circular dependency was detected". How can this be resolved.

 

Thanks

Mann

1 ACCEPTED SOLUTION
sturlaws
Resident Rockstar
Resident Rockstar

Hi, @Mann,

 

you can avoid the circular dependency by using Power Query/Transform data. First create an index column on your HR-table and call it e.g. key_hr. Then in your fact table, create a new column following this procedure: http://www.excelnaccess.com/lookup-between-2-dates-and-return-a-corresponding-value/ so that you have the corresponding key_hr in the fact table. 

 

Once the data is loaded to Power BI, you can create the relationship between the two tables on key_hr.

 

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

View solution in original post

5 REPLIES 5
v-easonf-msft
Community Support
Community Support

Hi , @Mann 

If help, try measures as below:

Column = CALCULATE(MIN('HR'[Attribute1]),FILTER(RELATEDTABLE(HR),'HR'[From Date]<='Fact'[Date]&&'HR'[To Date]>='Fact'[Date]))

It will show as below:

48.png

 

Pbix attached

 

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Ashish_Mathur
Super User
Super User

Hi,

I am surprised that your calculated column DAX formula is working fine.  The reason it should not be working is that you have not used the EARLIER() function and MIN() function should only work with a numeric input.  Anyways, I would use the Query Editor to merge the Attribute1 column info into the Fact Table.  However, in the HR Table, on 2/3/2020 for Ind ID A, there are two values for Attribute1 - abc and def.  Which one should we consider?  This problem is there for Ind ID C as well.  Ind ID B is fine.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi Ashish,

I created sample data to explain the issue better. I might have missed few things there. I think the intend is clear here that is to create relationship in DAX without going to power query.
sturlaws
Resident Rockstar
Resident Rockstar

Hi, @Mann,

 

you can avoid the circular dependency by using Power Query/Transform data. First create an index column on your HR-table and call it e.g. key_hr. Then in your fact table, create a new column following this procedure: http://www.excelnaccess.com/lookup-between-2-dates-and-return-a-corresponding-value/ so that you have the corresponding key_hr in the fact table. 

 

Once the data is loaded to Power BI, you can create the relationship between the two tables on key_hr.

 

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

Thanks @sturlaws

Since my fact table is huge and putting this in power query might slow it down further can this be done through DAX somehow?

Can we break circular dependency somehow in DAX.

Mann

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.