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

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.

  • GunnerJ's avatar
    GunnerJ
    Post Patron
    4 years ago

    AlexisOlson 

    That worked in getting rid of the circular dependency for those columns! Sadly one bit of bad news is that it seems to have passed the dependency onto another column. "DISC Running Total" is a cumulative sum working on an account level. Its saying its dependency is related to "Last Count" which you've provided. I know "Last Count" literally references it but I'm unsure how to make a cumulative total without calculate. Any help?

    DISC running Total =
    VAR LastNeededDt = CALCULATE(MAX('SO Attrition'[NEEDED_DT]))
    VAR countOfDISC =
    CALCULATE(
    SUM('SO Attrition'[add or disc]),
    FILTER(
    ALLSELECTED('SO Attrition'),
    'SO Attrition'[NEEDED_DT] <= LastNeededDt),
    'SO Attrition'[BI_ACCT] = EARLIER('SO Attrition'[BI_ACCT]))
    Return countOfDISC



    • 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?