Circular Dependency on Calculated Columns

[bsmartbkind]

Hello Everybody. Hope you can help me with this issue.

I created a calculated column with a simple code, as follows

EgrTotGen$Mix =
IF (
    TrxCxP[IDMoneda] = "VES",
    IF (
        [TasaVentaAH] = 0,
        DIVIDE ( TrxCxP[TOTAL GENERAL], [TasaMix], 0 ),
        DIVIDE ( TrxCxP[TOTAL GENERAL], [TasaVentaAH], 0 )
    ),
    TrxCxP[TOTAL GENERAL]
)

When I try to create another calculated column exactly as the previous one, but using another rate table, I get the Circular Dependency error.

EgrTotGen$BCV =
IF (
    TrxCxP[IDMoneda] = "VES",
    IF (
        [TasaVentaAH] = 0,
        DIVIDE ( TrxCxP[TOTAL GENERAL], [TasaVentaBCV], 0 ),
        DIVIDE ( TrxCxP[TOTAL GENERAL], [TasaVentaAH], 0 )
    ),
    TrxCxP[TOTAL GENERAL]
)

[abhijit123]

Create Calculated column for one 

& For Another one create measure.

[abhijit123]

or create measure instead of calculated column

[edhans · Accepted solution]

It is a complex issue.

• WHen you use a measure in another measure (your [TasaVentaAH] ) it is wrapped in an implicit CALCULATE.
• CALCULATE removes all filters, then reapplies the filters for the row context, so it creates a filter for every single column in the table when you use it in a Calculated column.
• Then you use CALCULATE (explicitly or implicitly) in another column.
• Then the first column you created is now referencing the 2nd one, and the 2nd one is referencing the first one.
• Boom. Circular reference.

There are exceptions to this, but it gets even more esoteric in how Power BI can find a unique field, and you cannot manually do this in Power BI at all. You can in SSAS and oddly enough, Excel's Power Pivot.

Bottom line, this is one of many reasons to not use calculated columns. Use measures instead.

In general, try to avoid calculated columns. There are times to use them, but it is rare. Getting data out of the source system, creating columns in Power Query, or DAX Measures are usually preferred to calculated columns. See these references:
Calculated Columns vs Measures in DAX
Calculated Columns and Measures in DAX
Storage differences between calculated columns and calculated tables
Creating a Dynamic Date Table in Power Query

[bsmartbkind]

Thanks, buddy. You gave me the solution by mentioning that the problem was to my measure [TasaVentaAH]. I have replaced the measure with a column and already the model is working. In any way it is clear to me that I must study a lot on the subject. Best regards.

Francisco.

Apologyze for my poor english.

[edhans]

Yes @bsmartbkind - This is a small but critical thing in how Calculated Columns work. There are two chapters on CALCULATE and Context Transition in this book and this calc column circular reference issue is 2-3 pages in that section. 

Glad I was able to help.