Forum Discussion

bsmartbkind's avatar
bsmartbkind
Advocate I
6 years ago
Solved

Circular Dependency on Calculated Columns

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 (         [Ta...
  • edhans's avatar
    6 years ago

    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