Need help To fix the circular dependency

[MiXiang]

ND

 

 

 

Can anyone help me to build the dax formula which can avoid the circular dependency? This is quite difficult because it include the recursion. But I think it can be solved because my answer are correct until the sixth month.

NP_B = Obtained the data from the Excel

ND_B = Obtained the data from the Excel

NS_B = Obtained the data from the Excel

NP = Previous ND+PreviousNS

ND = ND_B * (NP_R/NP_B-1)

NS = (NP_R*％)/3

NP_R = NP_B + NP

Can help me to build the dax formula for NP, ND, NS, NP_R to avoid circular dependency? Thanks a lot!!!

[lbendlin]

Are these measures or calculated columns?  Have you tried implementing this in Power Query instead?

[MiXiang]

I'm seeking assistance in creating a DAX formula within Power BI that can avoid circular dependencies. This challenge arises because my calculation involves recursion, but I'm optimistic that it can be resolved, as my results are accurate up to the sixth month.

Let's delve into the specifics:

1. Variables:
- NP_B: Data sourced from Excel.
- ND_B: Data sourced from Excel.
- NS_B: Data sourced from Excel.

2. Dependency Relationships:
- NP is dependent on the previous values of ND and NS.
- ND relies on ND_B, NP_B, and NP in its calculation.
- NS depends on NP_R and incorporates a percentage.

The crux of the issue lies in circular dependency:
- NP_R is utilized in both ND and NS calculations, creating a loop. This circular dependency is where I require guidance and a workaround solution tailored to Power BI.

In essence, I'm looking for a DAX formula that allows these interdependent variables to coexist without causing circular dependencies within Power BI.

Thanks a lot!!!

The formula for NP ND NS NP_R:

NP = Previous ND+PreviousNS

ND = ND_B * (NP_R/NP_B-1)

NS = (NP_R*％)/3

NP_R = NP_B + NP

[lbendlin]

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[MiXiang]

No. I just use in calculated columns and measures