Relationship between table with duration ranges

[Mann]

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 ID	Date
A	2/1/2020
A	2/12/2020
B	2/3/2020
C	2/4/2020
D	2/5/2020
B	2/6/2020
C	2/17/2020

HR Table

Ind ID	From Date	To Date	Attribute1
A	1/1/2020	2/3/2020	abc
A	2/3/2020	2/14/2020	def
B	2/1/2020	2/5/2020	ghf
C	1/4/2020	2/7/2020	dvd
D	2/1/2020	2/6/2020	bee
B	2/6/2020	2/10/2020	vfd
C	2/7/2020	2/19/2020	fnm

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

[v-easonf-msft]

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:

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]

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.

[Mann]

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 · Accepted solution]

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.

[Mann]

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