Forum Discussion

Mann's avatar
Mann
Resolver III
6 years ago
Solved

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

Need Help
  • sturlaws's avatar
    sturlaws
    6 years ago

    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.

5 Replies

  • sturlaws's avatar
    sturlaws
    Resident Rockstar
    6 years ago

    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's avatar
      Mann
      Resolver III
      6 years ago
      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
  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    6 years ago

    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's avatar
      Mann
      Resolver III
      6 years ago
      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.
  • v-easonf-msft's avatar
    v-easonf-msft
    Community Support
    6 years ago

    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.