cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

Circular depenedency error when using IF statement

Hi All,

 

I have two tables with Many to Many relations, I have created two calculated column on Table A, I have written a condition where I am comparing the values between an existing column and a calculated column in Table A by using IF statements, for which I am getting the circular dependency error.

 

TABLE A

ID ||  Start_Time ||  End_Time ||  15_Min_Time   Customer_Value  
1 ||   2020-01-02 10:01:00 ||  2020-01-02 10:08:00  ||  2020-01-02 10:00:00 ||    3
2 ||  2020-01-02 10:01:00 ||  2020-01-02 10:08:00 ||  2020-01-02 10:00:00 ||    1
3 ||  2020-01-02 10:06:00 ||  2020-01-02 10:12:00 ||  2020-01-02 10:00:00 ||    4
4 ||  2020-01-02 10:18:00 ||  2020-01-02 10:30:00 ||  2020-01-02 10:15:00 ||    1

 

TABLE B

TimeIn  ||  Info_Value ||   15_Min_Time
2020-01-02 10:00:00 ||  2  || 2020-01-02 10:00:00
2020-01-02 10:01:00 ||  1  || 2020-01-02 10:00:00
2020-01-02 10:02:00 ||  3  || 2020-01-02 10:00:00
2020-01-02 10:03:00 ||  4  || 2020-01-02 10:00:00
2020-01-02 10:04:00 ||  5  || 2020-01-02 10:00:00
2020-01-02 10:05:00 ||  1  || 2020-01-02 10:00:00

 

TABLE A and TABLE B are having M:M relation on 15_Min_Time

 

Calculated_Col_1 = AVERAGEX(

FILTER(Table B, Table B[timeIN] >= Table A[start_time] && Table B[timeIN] <= Table A[End_time]), Table B[Info_Value])

 

Calculated_Col_2 = 

CALCULATE(DISTINCTCOUNT(Table A[Id]),GROUPBY(Table A,Table A[start_time], Table A[End_time]),ALLEXCEPT(Table A,Table A[Calculated_Col_1]))

Calculated_Col_3 = IF( Table A[Customer_Value] <= Table A[Calculated_Col_2], 1, IF ( Table A[Customer_Value] > Table A[Calculated_Col_2], 1,3))
 
Error:-
Circular dependency was detected: Table A[Customer_Value], Table A[Calculated_Col_3], Table A[Customer_Value] 
 
I tried SWITCH() as well and got the same error.

 

1 REPLY 1
Highlighted
Community Support
Community Support

Hi @nikeshv ,

 

M:M relationship may cause multiple problems in calculation,you'd create a relationship between other fields which is a 1:M relationship.

BTW,could you pls advise me what your expected output is?

 


Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors