Forum Discussion
Relationship between table with duration ranges
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.
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.
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.