Circular Dependency in Calc Columns with USERELATIONSHIP & ALLEXCEPT

[RevOps608]

I've went around and around and I cant figure this circ dependancy out. I'm trying to create two calculated columns. '2023 Referral ACV' and '2024 Referral ACV'. The 2023 worked fine, the 24' is giving me the dependancy error.

I've used both of the following SQLBI references.

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

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

2023 Referral ACV = // Works Correctly

CALCULATE(

    SUM(Opportunity[Referral ACV]),

    Opportunity[Close_Year__c] = 2023,

    USERELATIONSHIP(Account[Acct Id], Opportunity[Channel_Referral_Firm.sf18id])

)

2024 Referral ACV = // Returns Circular Dependancy

CALCULATE(

        SUM(Opportunity[Referral ACV]),

        Opportunity[close_year__c] = 2024,

        USERELATIONSHIP(Account[Acct Id], Opportunity[Channel_Referral_Firm.sf18id]),

        ALL(Opportunity[Referral ACV])

)

2024 Referral ACV = // Version 2, Doesnt give me the Dependancy error but I get the same repeating Total down the column

CALCULATE(

        SUM(Opportunity[Referral ACV]),
        Opportunity[close_year__c] = 2024,

        USERELATIONSHIP(Account[Acct Id], Opportunity[Channel_Referral_Firm.sf18id]),

        ALLEXCEPT(Opportunity, Opportunity[Referral ACV]))

[RevOps608 · Accepted solution]

Figured it out. I read through this other post a ton but I was using 'ALL' wrong. Boom, winner.

https://community.fabric.microsoft.com/t5/Desktop/ALLEXCEPT-USERELATIONSHIP/m-p/517611

2024 Referral ACV =

CALCULATE(

        SUM(Opportunity[Referral ACV]),

        Opportunity[Close_Year__c] =2024,

        USERELATIONSHIP(Account[Acct Id], Opportunity[Channel_Referral_Firm.sf18id]),

        ALL(Account[2023 Referral ACV]))