Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Pablinho
Helper I
Helper I

Circular dependency when using calculated column

Hello!

 

This is my scenario. I have one table with % rates for different terms. My goal is to display a line graph that shows how the rate increases over time. So, if the rate in the first term was 1% and the rate in the second term was 2%, I want to see it it ploted as 1%, 3%.

My approach so far was to create a calculated column(Result) based on a Rate and Value columns, using the value as an index to keep adding the previous Rate to the next one. It worked so far.
My problem is that I manually entered the Rate values but want them to be dynamical, calculating them from another table where the data is.
What I tried is another calculated column that does that calculation and now I have the ActualRate.

When I tried to now substitute the new ActualRate for the original Rate in my Result column. I get a circular dependency error, so I got stuck there.

I tried replacing the ActualRate calculated column as a measure, but the thing is that it does not add the previous values, it just displays the term.
Here is my code for the Result calculated column:

Result =
VAR _currentRATE = Progression[Rate]
VAR _currentValue = Progression[Value]
VAR _sum = SUMX(FILTER('Progression', Progression[Value] <= _currentValue), Progression[Rate])
VAR _last0 = CALCULATE(MAX(Progression[Value]), FILTER(Progression, Progression[Value] < _currentValue && Progression[Value] = 0))
RETURN IF(_currentRATE = 0, 0,
SUMX(FILTER(Progression, Progression[Value] >= _last0 && Progression[Value] <= _currentValue), Progression[Rate]))

and my code for the ActualRate calculated column or measure:
ActualRate = if(
                 CALCULATE(sum(Masterfile[Awarded]),Masterfile[STATUS]="GRD") * 100 = 0,
                 0,CALCULATE(sum(Masterfile[Awarded]),Masterfile[STATUS]="GRD") * 100)
 

This is how my columns look like:

Pablinho_0-1692283849812.png

 

Pablinho_1-1692284099843.png

And this is the current Result (with the measure) agains the expected Result (with the Rate calculated column):

Pablinho_2-1692284250556.png

Any help is greatly appreciated! Thank you!



4 REPLIES 4
some_bih
Super User
Super User

Hi @Pablinho try the last part 

CALCULATE(sum(Masterfile[Awarded]),Masterfile[STATUS]="GRD") * 100 

put into variable and this replace in ActualRate.  





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

Proud to be a Super User!






Hello.

 

Thanks for the reply. I did the change to the ActualRate column, but the error persists. This is how the ActualRate column looks now:

ActualRate =
VAR _actualRate = CALCULATE(sum(Masterfile[Awarded]),Masterfile[STATUS]="GRD") * 100 
RETURN if(
    _actualRate = 0,
    0,_actualRate)
 
Thank you.

Hi @Pablinho is statement below return any value at all?

CALCULATE(sum(Masterfile[Awarded]),Masterfile[STATUS]="GRD") * 100 





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

Proud to be a Super User!






Yes, it gives the correct value but only for the ActualRate column. The error comes from the Result column when I try to use the ActualRate column value instead of the Rate one.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors