Circular Dependency in DAX calculated column::AAS model

Question

I am stuck in the situation where I am adding a new calculated column but it's prompting me the circular dependency. The first column is calculated like:

=VAR Denominator = ( Validation_Accounts_Agreements[CalculatedClosedDateVsFirstPurchaseDate] + 1 )
VAR Sales =
    CALCULATE (
        SUM ( SalesR48NBVBySolution[TotalSales] ),
        FILTER (
            SalesR48NBVBySolution,
            SalesR48NBVBySolution[invoicedate] >= Validation_Accounts_Agreements[CalculatedFirstAnchorPurchaseDateAfterGTWClosedDate]
        )
    )
VAR R6AnchorSales =
    CALCULATE (
        SUM ( SalesR48NBVBySolution[TotalSales] ),
        FILTER (
            SalesR48NBVBySolution,
            SalesR48NBVBySolution[invoicedate]
                >= EDATE (
                    Validation_Accounts_Agreements[CalculatedFirstAnchorPurchaseDateAfterGTWClosedDate],
                    -6
                )
                && SalesR48NBVBySolution[invoicedate] < Validation_Accounts_Agreements[CalculatedFirstAnchorPurchaseDateAfterGTWClosedDate]
                && SalesR48NBVBySolution[ClensedAnchorDesignation] = "Y"
        )
    )
RETURN
    IF ( ISBLANK ( R6AnchorSales ),
        IF ( NOT (ISBLANK ( Validation_Accounts_Agreements[CalculatedFirstAnchorPurchaseDateAfterGTWClosedDate] )
            )
                && ISBLANK ( R6AnchorSales ),
            DIVIDE ( Sales, Denominator ) * 12
        )
    )

The next column I want to create here is:

=VAR Denominator = ( Validation_Accounts_Agreements[CalculatedClosedDateVsFirstPurchaseDate_SLN] + 1 )
VAR Sales =
    CALCULATE (
        SUM ( SalesR48NBVBySolution[TotalSales] ),
        FILTER (
            SalesR48NBVBySolution,
            SalesR48NBVBySolution[invoicedate] >= Validation_Accounts_Agreements[CalculatedFirstPurchasedateAfterGTWClosedDate_SLN]
        )
    )
VAR R6AnchorSales =
    CALCULATE (
        SUM ( SalesR48NBVBySolution[TotalSales] ),
        FILTER (
            SalesR48NBVBySolution,
            SalesR48NBVBySolution[invoicedate]
                >= EDATE (
                    Validation_Accounts_Agreements[CalculatedFirstPurchasedateAfterGTWClosedDate_SLN],
                    -6
                )
                && SalesR48NBVBySolution[invoicedate] < Validation_Accounts_Agreements[CalculatedFirstPurchasedateAfterGTWClosedDate_SLN]
                && SalesR48NBVBySolution[ClensedAnchorDesignation] = "N"  &&  CALCULATE(MAX('CAM Alignment'[NodeCd_L3])=="C3-10-00015")

        )
    )
RETURN
    IF ( ISBLANK ( R6AnchorSales ),
        IF ( NOT (ISBLANK ( Validation_Accounts_Agreements[CalculatedFirstPurchasedateAfterGTWClosedDate_SLN] )
            )
                && ISBLANK ( R6AnchorSales ),
            DIVIDE ( Sales, Denominator ) * 12
        )
    )

Here is more info about my model::

In the above picture, the yellow highlighted columns I am using to create the calculated column 1 (calculated Annualization).The red circled columns are being used to create column 2 ( calculated Annualization_SLN).both sets of columns are almost similar (changes in the filter).But when I am trying to create column 2 ( calculated Annualization_SLN) that time I am getting this error of circular dependency; which is dependent on column 1.

Answer 1

In general, according to best-practice avoid extensively using Calculated Columns. Instead, add them in your data source (back-end) or use M-Query.

In order to answer your question precisely, you need to share more information about your model (i.e. relationships). However, we can provide you some guidelines referring to this article in order to avoid circular dependencies.

Quoting the conclusion of the article:

Most of the time, circular dependencies occur when you use calculated tables. You can easily avoid them by paying attention to your choice of functions. The difference between DISTINCT and VALUES, or between ALL and ALLNOBLANKROW is a subtle difference. But once you get used to it, your code will be safer when it comes to relationships and circular references.