cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
npatten Helper II
Helper II

Circular Dependency in a Fundraising DAX formula

Hi--- I'm wondering if someone can help me with figure out a formula. I work at a childrren't hosptial and I'm creating a predictive model for fundraising behaviour. Normally I'd do this in Excel, but I'm in the process of doing all my modeling in the DAX environemnt. Anyway, part of the model is scoring donors based on a set of simple calculations using average gift size and donation frequency. These calcuations (ie, calculated columns) are working fine.

 

Another part of the model is segmenting donor's average gift size into a set of giving bands. I'm using the static segmentation formula principles developed by the duo at sqlbi.com (you can check it here http://www.daxpatterns.com/static-segmentation/), and I'm getting a 'circular dependecy' error when I try to run it. (Note: I've run the segmentation formula successfully on other columns). The segmentation calculation I'm tying to make is based on set of calculated columns that determine the number of giving years using a CALCULATE(COUNTAX formula, and a simple averaging column based on the total giving years, divided by the total giving . The funny thing is I can successfully run the segmentation formula if I use the total giving calculation, but when I duplicate the formula and swap in the average gift calculation it returns the error. Here's the formula

 

AvgGiftSeg = CALCULATE(VALUES(ScoringSegments[GivingRange]), FILTER(ScoringSegments, [AvgGiftSize] >= ScoringSegments[MinValue] && [AvgGiftSize] < ScoringSegments[MaxValue]))

 

I've read a number of discussions on circular dependence from the Power BI community, sqlbi, and powerpivotpro sites, and one potential solution was to add an ID row, which I've done but doesn't resolve the issues. Also, another solution suggested using the ALLEXCPET function, which I understand conceptually but not practically. I'm coming at DAX from a non-compsci background fyi.

 

 

Thanks in advance for any help.

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft Eric_Zhang
Microsoft

Re: Circular Dependency in a Fundraising DAX formula

@npatten

 

As the table GiftDetails and CCCYear is in 1:1 relationship, change the AvgGiftSize a little, then the AvgGiftSeg should work.

 

AvgGiftSize =
GiftDetails[TotalGiving]
    / LOOKUPVALUE (
        CCCYear[Frequency],
        CCCYear[Constituent ID], GiftDetails[Constituent ID]
    )

View solution in original post

5 REPLIES 5
Microsoft Eric_Zhang
Microsoft

Re: Circular Dependency in a Fundraising DAX formula

@npatten

 

Are the [AvgGiftSize],[MinValue],[MaxValue] calculated columns? I just can't guess the exact cause of your problem, could you please upload the pbix?

 

By the way, the formula "AvgGiftSeg = CALCULATE(VALUES(ScoringSegments[GivingRange])" would return error when there're more than one value, try to replace VALUES with an aggregation, like SUM or AVERAGE accordingly.

npatten Helper II
Helper II

Re: Circular Dependency in a Fundraising DAX formula

Hi-

 

Only the [AvgGiftSize] is a calculated column. The others are column headings from a table. I can get the formula to work with other calculated columns, just not the [AvgGiftSize]. I'll upload the pbilx asap. Thank you!

npatten Helper II
Helper II

Re: Circular Dependency in a Fundraising DAX formula

Here's a link to the pbix doc. I couldn't find a way to upload the attachment, so i'm hosting it on my onedrive account. givinganalysis

Microsoft Eric_Zhang
Microsoft

Re: Circular Dependency in a Fundraising DAX formula

@npatten

 

As the table GiftDetails and CCCYear is in 1:1 relationship, change the AvgGiftSize a little, then the AvgGiftSeg should work.

 

AvgGiftSize =
GiftDetails[TotalGiving]
    / LOOKUPVALUE (
        CCCYear[Frequency],
        CCCYear[Constituent ID], GiftDetails[Constituent ID]
    )

View solution in original post

npatten Helper II
Helper II

Re: Circular Dependency in a Fundraising DAX formula

Thank you for helping out with this issue. If you have some time, it would be helpful to know why you are using a LOOKUPVALUE instead of a CALCULATE formula. I'm still a bit of a newbie to DAX.It would be helpful to other people viewing this solution, I think.  Thank you!

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors