Forum Discussion
Ways to avoid circular dependency
Hello! I am trying to create a calculated column by dividing one column from table 1 by another column from table 2. I use the following:
=sumx(naturaljoin(table1,table2),column 1/ column2)
however I get circular dependency error. What can I add to my syntax to create a workaround?
the issue is that I cannot use a measure because I need to slice and dice my data and these two columns do not have direct relationship.
Usually I use calculate and then all or removefilter function. But I can't use it because it this case I'm dividing kg by % and with calculate I have to use an aggregate function and I can't use an aggregate function with %
thank you
Hi KK111 ,
You can try to use LOOKUPVALUE function to create a calculated column in Table 1, then use SUMX function to calculate.
Column 2 = LOOKUPVALUE(Table2[Sales],Table2[Product],Table1[Product]) Column 3 = SUMX(Table1,Table1[Column 2]/Table1[Quantities])
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 Reply
Hi KK111 ,
You can try to use LOOKUPVALUE function to create a calculated column in Table 1, then use SUMX function to calculate.
Column 2 = LOOKUPVALUE(Table2[Sales],Table2[Product],Table1[Product]) Column 3 = SUMX(Table1,Table1[Column 2]/Table1[Quantities])
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.