need workaround for circular dependency

[Anonymous]

I have the data model as shown in the image below. 

I have two columns called A and B in my fact table, and a column named "employed more than 3 months" in my employee dimension table. (please ignore the other columns)

What I need is to have a column as following in my fact table: 

C= A- B

and then I need to have a column which produce the result as follow: 

result= IF [C <= 0 AND [employyed more than 3 months] = 'True'
THEN [C]
ELSE 0
END

I tried to create a calculated column with CALCULATE() function, but I receive a circular dependency error. 

can someone help me create this?

[v-cherch-msft · Accepted solution]

Hi @Anonymous 

I would suggest you create only one column like below.For further,please refer to below article.

https://www.sqlbi.com/articles/understanding-circular-dependencies/

https://www.sqlbi.com/articles/avoiding-circular-dependency-errors-in-dax/

Column =
IF (
    CALCULATE ( SUM ( [A] ) - SUM ( [B] ) ) < 0
        && RELATED ( Dim_EmployeeData[employyed more than 3 months] ) = "True",
    CALCULATE ( SUM ( [A] ) - SUM ( [B] ) ),
    0
)

Regards,