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.
