Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
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

@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
Eric_Zhang
Employee
Employee

@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.

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

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

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!

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!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.