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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
rpinxt
Impactful Individual
Impactful Individual

Why do I get Circular Dependancy

Making calculated columns my first one is this :

IF('AVN Rework Merge'[Flow] = "Reworked" && [Amt USD] > -50,[Quantity],0)

 

It works fine and gives me the quantity that I expected.

 

Now I want a similar one that gives met the USD amount instead of the Quantity so I made :

IF('AVN Rework Merge'[Flow] = "Reworked" && [Amt USD] > -50,[Amt USD],0)

 

But then suddenly it says I have a circular dependancy and I cannot understand why it is doing this.

rpinxt_0-1663576148356.png

 

2 ACCEPTED SOLUTIONS
m_alireza
Solution Specialist
Solution Specialist

@rpinxt Instead of referencing the measure [FinAmtUSD], try referencing the column directly instead.

so:

Reworked USD < 50 = IF('AVN Rework Merge'[Flow] = "Reworked" && 'AVN Rework Merge'[FinAmtUSD] > -50,'AVN Rework Merge'[FinAmtUSD],0)

View solution in original post

@rpinxt I agree with @m_alireza. Also, any specific reason why you went with Calculated Columns instead measures?
I rewrote the logic as a measure for you. Check if this meets your requirement

Reworked Qty <50__ = 
VAR Result = CALCULATE(
     [Quantity],
     FILTER('AVN Rework Merge', 'AVN Rework Merge'[Flow] = "Reworked" &&  [Amt USD] > -50)
     )
RETURN
COALESCE(Result,0)

View solution in original post

15 REPLIES 15
m_alireza
Solution Specialist
Solution Specialist

@rpinxt Instead of referencing the measure [FinAmtUSD], try referencing the column directly instead.

so:

Reworked USD < 50 = IF('AVN Rework Merge'[Flow] = "Reworked" && 'AVN Rework Merge'[FinAmtUSD] > -50,'AVN Rework Merge'[FinAmtUSD],0)

@rpinxt I agree with @m_alireza. Also, any specific reason why you went with Calculated Columns instead measures?
I rewrote the logic as a measure for you. Check if this meets your requirement

Reworked Qty <50__ = 
VAR Result = CALCULATE(
     [Quantity],
     FILTER('AVN Rework Merge', 'AVN Rework Merge'[Flow] = "Reworked" &&  [Amt USD] > -50)
     )
RETURN
COALESCE(Result,0)
rpinxt
Impactful Individual
Impactful Individual

@SivaMani @m_alireza I want to thank you both because both solutions seem to work! 😁

 

Well I chose for calculated column because I merged two sources and thought to get the correct amount on every line a calculated column would be better.

Also I tried a measure before but could not succeed 😑

 

Must say I do not fully understand your measure.

What does COALESCE do?

 

And what would you guys think is best in ways of performance?

Would the measure be better (lighter) on the data then the calculated columns?

@rpinxt, You're welcome!

COALESCE - If the first expression is BLANK, it returns zero. It is nothing but IF(ISBLANK(Result),0, Result )

 

Calculated Columns vs Measures -Both have their own use cases.

Refer the below URLs for more info - https://community.powerbi.com/t5/Desktop/column-vs-measure/m-p/13203/highlight/true#M3305

https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/#:~:text=The%20difference%20is....

 

rpinxt
Impactful Individual
Impactful Individual

Thanks!

rpinxt
Impactful Individual
Impactful Individual

@SivaMani 

Well yes both logics here are Calculated Columns:

 

Calculated Column 1:

FirstColumn = IF('AVN Rework Merge'[Flow] = "Reworked" && [Amt USD] > -50,[Quantity],0)

 

Calculated Column 2:

SecondColumn = IF('AVN Rework Merge'[Flow] = "Reworked" && [Amt USD] > -50,[Amt USD],0)

 

And yes they are looking at the same columns but column1 returns the Quantity and column2 should return the USD Amount.

 

But as I can see they are not referencing eachother....

@rpinxt, I couldn't reproduce the issue based on the info you provided. Can you share your pbix file with sample data (remove any sensitive data), if possible?

rpinxt
Impactful Individual
Impactful Individual

Could try to do that, but how can one add the pbix file here?

Upload it to OneDrive or Google Drive and share the URL

rpinxt
Impactful Individual
Impactful Individual

@rpinxt, What about the 'AVN Rework Merge'[FinAmtUSD] and 'AVN Rework Merge'[Flow]? Are they direct columns from the source or calculated in Power BI?

rpinxt
Impactful Individual
Impactful Individual

FinAmtUSD is directly from the source.

 

Flow is also directly from the source be it that it comes from an excel file.

The excel file was merged with the source based on a similar field in both the source and the excel file (FlowKey).

 

So in base the 2 fields are not calculated.

rpinxt
Impactful Individual
Impactful Individual

@SivaMani 

The Amt USD measure refers to a field from the table like :

Amt USD = SUM('AVN Rework Merge'[FinAmtUSD])

@rpinxt, As you see in the error message, there is another column 'AVN Rework Merge'[Column] - It might be referencing the same columns in its expression.  Circular Dependency is detected whenever two columns referenced each other. Check your calculated columns logic.

SivaMani
Resident Rockstar
Resident Rockstar

@rpinxt, What is the logic for the [Amt USD] measure?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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