Using measures and calculated columns

[Carla_01]

I've created a measure in PowerBI with:

Net Unrealized FV Gain Loss Threshold Check =

IF(

    SUM(Table[NET_UNREAL_FV_GAIN_LOSS]) < -50000000,

    "yes",

    "no"

)

And I've used this measure in a calculated column:

Profit Loss Before Income_afterAdjustments =

IF(

    [Net Unrealized FV Gain Loss Threshold Check] ="yes",

    Table[REPORTING_UNIT_PROFIT_LOSS_BEFORE_INCOME] - Table[NET_UNREAL_FV_GAIN_LOSS],

    Table[REPORTING_UNIT_PROFIT_LOSS_BEFORE_INCOME]

)

After that, I've several other calculated columns where I need to use the value output of this calculated column "Profit Loss Before Income_afterAdjustments"

Example case:

Routine Profits Test1 =

IF(

    Table[SBIE_Amount] >= (Table[Profit Loss Before Income_afterAdjustments]+ Table[GOODWILL_IMPAIRMENT_ADJ]),

    "yes",

    "no"

)

However, whenever I want to use the Profit Loss Before Income_afterAdjustments I get an error with "circular dependency". Can someone give me an idea on how to solve this issue?

[johnt75 · Accepted solution]

The circular dependency is because when you calculate the measure in the first calculated column context transition moves all the columns of 'Table' from a row context to a filter context, so that column depends on all the other columns of 'Table'. When you try to add the second column that would mean that the second column would need to be included in the context transition performed in the first column, and so the first column depends on the second column. But the second column depends on the first column as it is using that value, hence the circular dependency.

To avoid the circular dependency make sure that only the columns needed are included in the context transition, ideally each row would have a unique identifier in which case you can use something like

Profit Loss Before Income_afterAdjustments =
IF (
    CALCULATE (
        [Net Unrealized FV Gain Loss Threshold Check],
        ALLEXCEPT ( 'Table', 'Table'[ID] )
    ) = "yes",
    'Table'[REPORTING_UNIT_PROFIT_LOSS_BEFORE_INCOME] - 'Table'[NET_UNREAL_FV_GAIN_LOSS],
    'Table'[REPORTING_UNIT_PROFIT_LOSS_BEFORE_INCOME]
)

[rajendraongole1]

Hi @Carla_01  -  you can avoid using the measure in the calculated column and instead replicate the logic directly within the calculated column.

Profit Loss Before Income_afterAdjustments =
IF(
SUM(Table[NET_UNREAL_FV_GAIN_LOSS]) < -50000000,
Table[REPORTING_UNIT_PROFIT_LOSS_BEFORE_INCOME] - Table[NET_UNREAL_FV_GAIN_LOSS],
Table[REPORTING_UNIT_PROFIT_LOSS_BEFORE_INCOME]
)

And then, your Routine Profits Test1 calculated column would be

Routine Profits Test1 =
IF(
Table[SBIE_Amount] >=
(Table[REPORTING_UNIT_PROFIT_LOSS_BEFORE_INCOME] -
IF(SUM(Table[NET_UNREAL_FV_GAIN_LOSS]) < -50000000,
Table[NET_UNREAL_FV_GAIN_LOSS], 0)
+ Table[GOODWILL_IMPAIRMENT_ADJ]),
"yes",
"no"
)

Hope this helps 

[amitchandak]

@Carla_01 , You should not use measure in calculated column, They are static in natures and will not take slicer values.
If you need bucketing means measure as dimension

Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-k

Dynamic segmentation -Measure to Dimension conversion: https://youtu.be/gzY40NWJpWQ

Customer Retention with Dynamic Segmentation, New/Lost/Retain Customer Count: https://youtu.be/EyL7KMw877Q

Power BI ABC Analysis using Window function, Dynamic Segmentation: https://youtu.be/A8mQND2xSR4

For Additional rows in P&L

Power BI Formatted Profit & Loss Statements | Power BI Balance Sheets | Power BI Custom Sub Total and Blank Rows: https://www.youtube.com/watch?v=C9K8uVfthUU&list=PLPaNVDMhUXGYo50Ajmr4SgSV9HIQLxc8L&index=118