Forum Discussion

GunnerJ's avatar
GunnerJ
Post Patron
4 years ago
Solved

Help with Circular Dependency

First I want to say that I've looked through several of the other post related to cirular dependency but they haven't helped me correct this issue. The two calculated columns below create a circular ...
Need Help
Tutorial Requests
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    I think you can avoid this by eliminating the context transition induced by CALCULATE.

     

    See if these versions work any better:

    Last SO =
VAR FilteredTable =
    FILTER (
        'SO Attrition',
        'SO Attrition'[BI_ACCT] = EARLIER ( 'SO Attrition'[BI_ACCT] )
            && 'SO Attrition'[NEEDED_DT] < EARLIER ( 'SO Attrition'[NEEDED_DT] )
            && 'SO Attrition'[SRV_MAP_LOC] = EARLIER ( 'SO Attrition'[SRV_MAP_LOC] )
            && 'SO Attrition'[BI_SO_TYPE_CD] <> EARLIER ( 'SO Attrition'[BI_SO_TYPE_CD] )
    )
RETURN
    MAXX ( FilteredTable, 'SO Attrition'[BI_SO_TYPE_CD] )
    Last Count =
VAR FilteredTable =
    FILTER (
        'SO Attrition',
        'SO Attrition'[BI_ACCT] = EARLIER ( 'SO Attrition'[BI_ACCT] )
            && 'SO Attrition'[NEEDED_DT] < EARLIER ( 'SO Attrition'[NEEDED_DT] )
    )
RETURN
    MAXX ( FilteredTable, 'SO Attrition'[DISC running Total] )

     

    It's possible to get it to work with CALCULATE but easier to mess up, especially if you're not comfortable with context transition concepts.

AlexisOlson's avatar
AlexisOlson
Super User
4 years ago

You could rewrite this one like I did with the others.

 

Here's a CALCULATE version too that I think should work:

 

DISC running Total =
CALCULATE (
    SUM ( 'SO Attrition'[add or disc] ),
    ALLEXCEPT ( 'SO Attrition', 'SO Attrition'[BI_ACCT] ),
    'SO Attrition'[NEEDED_DT] <= EARLIER ( 'SO Attrition'[NEEDED_DT] )
)

 

GunnerJ's avatar
GunnerJ
Post Patron
4 years ago

AlexisOlson it worked! thank you! 

You referenced Context transitions. Just for future knowledge do you have a best source to get more familiar?