cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
GunnerJ
Helper V
Helper V

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 dependency. "Last SO" was created first and I'd prefer to not change it if at all possible. "Last Count" is looking at a cumulative total and trying to show what the value was before the current row where account is the same and the date came before. Is there some kind of filter magic we can use to keep the same functionality but to get rid of the error?

 

 

Last SO =
CALCULATE(LASTNONBLANK('SO Attrition'[BI_SO_TYPE_CD],1),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])))

 

Last Count =
CALCULATE(LASTNONBLANK('SO Attrition'[DISC running Total],1),FILTER('SO Attrition', 'SO Attrition'[BI_ACCT] = EARLIER('SO Attrition'[BI_ACCT]) && 'SO Attrition'[NEEDED_DT] < EARLIER('SO Attrition'[NEEDED_DT])))
1 ACCEPTED SOLUTION
AlexisOlson
Super User
Super User

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.

View solution in original post

5 REPLIES 5
AlexisOlson
Super User
Super User

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 

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



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] )
)

 

@AlexisOlson it worked! thank you! 

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

I included a link to an article upon my first mention.

 

Here's that link again along with another more recently written article:
https://www.sqlbi.com/articles/understanding-context-transition/

https://www.burningsuit.co.uk/context-transition-where-the-row-context-becomes-a-filter-context/

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors