topic How to avoid circular dependency when I the value of the column is its previous value plus other val in DAX Commands and Tips

How to avoid circular dependency when I the value of the column is its previous value plus other val

wellidyalmeida — Fri, 16 Sep 2022 21:02:30 GMT

Hi all.

I'm facing with a circular dependency error and all I tried not solved.
I have 3 columns:
Column A
Column B
Column C

The value of the column C is
(A + [previous value of C]) - B

Some trick to achieve this?

This image show how I did this in excel

Thanks in advance

Re: How to avoid circular dependency when I the value of the column is its previous value plus other val

tamerj1 — Sat, 17 Sep 2022 07:52:38 GMT

Hi wellidyalmeida
This is called recursive calculation which is not supported by Power Bi as the DAX engine evaluates the calculated column or the measure at once.
However, there is a workaround and in this specific case it is not complicated. For this to work properly you need to specify the following:
1. The starting value for Column C

2. What you state as Column A, Column B & Column C, are they columns or measures?
Attached is a sample file tailored to match your requirement for both calculated column and measure options

C = VAR StartingValue = 2000 VAR MinDate = MIN ( Table1[Date] ) VAR CurrentDate = Table1[Date] VAR FilteredTable = FILTER ( Table1, Table1[Date] > MinDate && Table1[Date] <= CurrentDate ) VAR RecursiveValue = SUMX ( FilteredTable, [A] - [B] ) RETURN StartingValue + RecursiveValue

Measure C = VAR StartingValue = 2000 VAR SelectedDates = ALLSELECTED ( Table2[Date] ) VAR MinDate = MINX ( SelectedDates, Table2[Date] ) VAR CurrentDate = MAX ( Table2[Date] ) VAR FilteredTable = FILTER ( SelectedDates, Table2[Date] > MinDate && Table2[Date] <= CurrentDate ) VAR MeasuresTable = ADDCOLUMNS ( FilteredTable, "@A", [Measure A], "@B", [Measure B] ) VAR RecursiveValue = SUMX ( MeasuresTable, [@A] - [@B] ) RETURN StartingValue + RecursiveValue

Re: How to avoid circular dependency when I the value of the column is its previous value plus other val

wellidyalmeida — Thu, 29 Sep 2022 20:44:09 GMT

Thank you, tamerj1. This solved the problem.

Re: How to avoid circular dependency when I the value of the column is its previous value plus other val

wellidyalmeida — Mon, 10 Oct 2022 17:35:21 GMT

Hi, tamerj1 , I need your help 🙂

The value of Column C is not correct.

I've been debuged every value used in the calculation, unfortunelly, I coundn´t understand what is wrong.

I used the COLUMN SOLUTION.

Can you help me, please?

I attached pbix and explaning it:

https://drive.google.com/file/d/1eX9hO1uNTAd1gaV3i57e-mvZ1kiqllfk/view?usp=sharing

Starting value is 359606 and come from the 'saldo inicial' table.

The calculated column is in the 'contas_vw' table in the column 'C'

The expected value is 181.737,87 but I got 182.367,83 as you can see in this image

Re: How to avoid circular dependency when I the value of the column is its previous value plus other val

wellidyalmeida — Tue, 11 Oct 2022 16:46:52 GMT

Solved!

The original solution from tamerj1 is correct.

The sequence that calculated columns are evaluated caused the problem. My column C, beside the recursive calculation, has conditionals that gave me headache.