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
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
Solved! Go to 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.
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:
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.
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.
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.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
108 | |
108 | |
108 | |
90 | |
61 |
User | Count |
---|---|
171 | |
138 | |
132 | |
102 | |
86 |