Forum Discussion
Avoid Circular Dependency Error With Dependent Measures
I'm a novice with dax, so forgive me if this is a stupid question...
My table looks like this:
| Period | BI | Prod | Demand | EI |
| 202310 | 3200 | 3000 | 2200 | 4000 |
| 202311 | 4000 | 5000 | 3200 | |
| 202312 | 4000 | 4520 | ||
| 202401 | 6000 | 4225 | ||
| 202402 | 7500 | 6200 |
This is what I want:
Period | BIMeasure | EIMeasure | Prod | Demand | Proj EI | Proj BI |
| 202310 | IF BI is blank, Proj BI | IF EI is blank, Proj EI | 3000 | 2200 | (BIMeasure+Prod)-Demand | Proj EI of Period:202309 |
| 202311 | IF BI is blank, Proj BI | IF EI is blank, Proj EI | 5000 | 3200 | (BIMeasure+Prod)-Demand | Proj EI of Period:202310 |
| 202312 | IF BI is blank, Proj BI | IF EI is blank, Proj EI | 4000 | 2200 | (BIMeasure+Prod)-Demand | Proj EI of Period:202311 |
| 202401 | IF BI is blank, Proj BI | IF EI is blank, Proj EI | 6000 | 2352 | (BIMeasure+Prod)-Demand | Proj EI of Period:202312 |
| 202402 | IF BI is blank, Proj BI | IF EI is blank, Proj EI | 7500 | 6200 | (BIMeasure+Prod)-Demand | Proj EI of Period:202401 |
I keep getting a circular reference error, which I understand, but I can't seem to find a way to avoid it.
2 Replies
can you please share your code?
one thing i would suggest is that if you can create the logic for these columns in power query, then do that there, but it all depends on how you are deriving this information.
Proj_BI = CALCULATE( [Proj_EI], FILTER( ALL('Table'[Period]), 'Table'[Period] = MAX('Table'[Period]) - 1 ) ) Proj_EI = ([Proj_BI] + [Prod]) - [Demand] BIMeasure = IF(ISBLANK(SUM('Table'[BI])), [Proj_BI], SUM('Table'[BI])) EIMeasure = IF(ISBLANK(SUM('Table'[EI])), [Proj_EI], SUM('Table'[EI]))This is the code.
The calculation is only required at this level of aggregation, on the power query side the data is more granular.
