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

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