Circular Dependencies

[Anonymous]

Equity Purchase =

VAR A =

    IF(

        'Deal Apr 15- Sep15A'[DL_CLASS] = 1 &&

        'Deal Apr 15- Sep15A'[DEAL_CD] = 4 &&

        'Deal Apr 15- Sep15A'[INSTR_SUB_TYPE] IN {"EQ", "EU", "NC", "PT", "RF"},

        [SUM NET AMT Sale],

        BLANK()

    )Return

    A

Equity Sales =

VAR B =

    IF(

        'Deal Apr 15- Sep15A'[DL_CLASS] = 1 &&

        'Deal Apr 15- Sep15A'[DEAL_CD] = 3 &&

        'Deal Apr 15- Sep15A'[INSTR_SUB_TYPE] IN {"DS", "ET", "IS", "OM", "LS", "ES", "BD", "BN", "CCD", "CD", "CP", "DB", "FD", "GS", "PR", "SR", "ZD"},

        [SUM NET AMT Sale],

        BLANK()

    )

    Return

    B

Equity Purchase and Equity Sales are two calculated columns in Power BI.
Getting the correct value for Equity Purchase.But while executing Equity Sales getting an error of circular dependencies.
If I create a measure instead of a column it did not accept columns from the table.
I want to calculate Equity Purchase - Equity Sales.

[Anonymous · Accepted solution]

Problem Solved.

Instead of creating calculated columns, I have created measures.
Like this.
Equity Purchase Measure =
VAR A =
IF(
'Deal Apr 15- Sep15A'[DL_CLASS] = 1 &&
'Deal Apr 15- Sep15A'[DEAL_CD] = 4 &&
'Deal Apr 15- Sep15A'[INSTR_SUB_TYPE] IN {"EQ", "EU", "NC", "PT", "RF"},
[SUM NET AMT Sale],
BLANK()
)
RETURN
A

[Anonymous]

Then what is the solution?

[Ashish_Mathur]

Hi,

Share some data and show the expected result.

[Anonymous]

https://www.dropbox.com/scl/fi/q5v16m78oplgd41zi02gn/Sample.xlsx?dl=0&rlkey=lnthochv65j7zrgc1jf5yock...

You can download data from above link.                                                          

Equity Purchase - Data with other than DS,ET. IS OM,LS, ES,BD ,BN,CCD,CD,CD,CP DB,FD, GS,FD,PR ,SR,ZD from colunm (INSTR_SUB_TYPE) and DL_CLASS where 1 code and DEAL_CD with 4 code.


Equity Sale - Data with DS,ET. IS OM,LS, ES,BD ,BN,CCD,CD,CD,CP DB,FD, GS,FD,PR ,SR,ZD from colunm (INSTR_SUB_TYPE) and DL_CLASS where 1 code and DEAL_CD with 3 code.


Net Investment = Equity Purchase - Equity Sale

[lbendlin]

You are using measures inside your column definitions. That won't work.

[Anonymous]

Equity Sales =

VAR B =

    IF(

        'Deal Apr 15- Sep15A'[DL_CLASS] = 1 &&

        'Deal Apr 15- Sep15A'[DEAL_CD] = 3 &&

        'Deal Apr 15- Sep15A'[INSTR_SUB_TYPE] IN {"DS", "ET", "IS", "OM", "LS", "ES", "BD", "BN", "CCD", "CD", "CP", "DB", "FD", "GS", "PR", "SR", "ZD"},

        SUM('Deal Apr 15- Sep15A'[NET_AMT]),

        BLANK()

    )

    Return

    B

If I use this instead of measure, getting the wrong values.