topic Re: Circular! Circular! Dependency! in measure in DAX Commands and Tips

Circular! Circular! Dependency! in measure

eddEdwards — Tue, 09 May 2023 16:56:30 GMT

Hallo Community,My Brain is almost full trying to fugure out this dax puzzle...

BalEOQ = contribution+Interest Earned -FixedExp-Withdrawals -Variable Expenses
Interest Earned (EOQ)=((BalanceBOQ+contribution)-[FixedExpBOQ))*Rate

How posible way can I write this dax formular in order to avoid the circular dependencey?

Re: Circular! Circular! Dependency! in measure

Anonymous — Thu, 11 May 2023 02:03:00 GMT

Hi eddEdwards ,

A circular dependency is detected whenever two objects reference each other, in such a way that Power BI cannot process the objects.

Please check the two blogs below for the causes and solutions.

Understanding circular dependencies in DAX - SQLBI

Avoiding circular dependency errors in DAX - SQLBI

The other PREVIOUSQUARTER() returns a single column table, and generally we use IN to determine if a value is in the table, or something like this.

CALCULATE((<Expression>,ALLSELECTED('project'),PREVIOUSQUARTER('project'[Date]))

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data in the Power BI Forum

Re: Circular! Circular! Dependency! in measure

lbendlin — Thu, 11 May 2023 03:02:22 GMT

Show the code for all four measures that are used in the InterestEOQ measure.

Re: Circular! Circular! Dependency! in measure

eddEdwards — Mon, 22 May 2023 12:14:06 GMT

Let me show you in Excel the intire workfow so that you can understand where I am bumping to the circular Dependency

Re: Circular! Circular! Dependency! in measure

eddEdwards — Mon, 22 May 2023 13:37:00 GMT

Re: Circular! Circular! Dependency! in measure

eddEdwards — Mon, 22 May 2023 13:47:57 GMT

These are measures I have used

.........................

BalanceEOQ = [Contributions]+[Withdrawals]+[Interest]-[FixedExp]-[VariableExp]

Contributions = SUM('Project'[Contribution])

Withdrawals = SUM(Project[Withdrawal])

FixedExp = 10

VariableExp = MAX(0.02*[Contributions],0)

BalBOQ = MAX(
Var BalanceBOQ=PREVIOUSQUARTER
(Project[Date] )
RETURN
CALCULATE(
[BalanceEOQ],
FILTER(ALLSELECTED(Project), 'Project'[date]= BalanceBOQ)
),
0)

Rate = 4*((1+0.02)^(0.25)-1)

For me to get interest Earned..........
Balance BOQ= contribution+Interest Earned(EOQ)-[FixedExpBOQ-Withdrawals(EOQ)-Variable Expenses(EOQ)
Interest Earned(EOQ)=((BalanceBOQ+contribution)-[FixedExpBOQ))*Rate

.........

How do I Get the Interest earned to add to balance or How can I get the Excel Example to work in Power Bi?

Re: Circular! Circular! Dependency! in measure

lbendlin — Tue, 23 May 2023 01:13:17 GMT

you may want to reduce the problem to the minimum compound process to see if that works. Then you can add your complications back in one by one

this may be good for guidance: Compound Growth using DAX - Excelerator BI