Circular Dependency Using Measures

[curtismob]

Hello,

I'm thinking what I am trying to accomplish can be done, but I'm not sure how to get around my Circular Dependency problem.

First I have this very basic calculated column below, which is used in the _mCost/SQFT Avg Feb measure:

Cost Per SQFT = DIVIDE(Directs[Cost], RELATED(Plan[Liveable Sqft]), 0)

This measure example takes it a step further by adding a montly time dimension to cost/sqft:

_mCost/SQFT Avg Feb =
VAR
    YearSelected = '_Date Time Slicer'[_mYear Selected]
VAR
    PlanCostSQFT =
            IF(YearSelected = YEAR(NOW()) && MONTH(NOW()) < 2,
            //THEN
            CALCULATE(SUM(Directs[_cConstant_Zero])),
            //ELSE       
            CALCULATE(SUM(Directs[Cost Per SQFT]),
                FILTER(Directs, Directs[Effective] <= DATE(YearSelected, 2, 5) &&
                       Directs[Expiration] >= DATE(YearSelected, 2, 5))))
VAR
    PlanCount = CALCULATE(DISTINCTCOUNT(Plan[Plan_ID])) 
VAR
    CostPerSqft =  CALCULATE(DIVIDE(PlanCostSQFT, PlanCount, 0))         
RETURN
IF(CostPerSqft < 50, Directs[_mCost/SQFT First Over $50], CostPerSqft)

The Directs[_mCost/SQFT First Over $50] measure is to find the first monthly cost/sqft over $50 and use it as a default where a monthly cost/sqft is less than $50.

_mCost/SQFT First Over $50 =
    IF(Directs[_mCost/SQFT Avg Jan] > 50, Directs[_mCost/SQFT Avg Jan],
    IF(Directs[_mCost/SQFT Avg Feb] > 50, Directs[_mCost/SQFT Avg Feb],
    IF(Directs[_mCost/SQFT Avg Mar] > 50, Directs[_mCost/SQFT Avg Mar],
    IF(Directs[_mCost/SQFT Avg Apr] > 50, Directs[_mCost/SQFT Avg Apr],
    IF(Directs[_mCost/SQFT Avg May] > 50, Directs[_mCost/SQFT Avg May],
    IF(Directs[_mCost/SQFT Avg Jun] > 50, Directs[_mCost/SQFT Avg Jun],
    IF(Directs[_mCost/SQFT Avg Jul] > 50, Directs[_mCost/SQFT Avg Jul],
    IF(Directs[_mCost/SQFT Avg Aug] > 50, Directs[_mCost/SQFT Avg Aug],
    IF(Directs[_mCost/SQFT Avg Sep] > 50, Directs[_mCost/SQFT Avg Sep],
    IF(Directs[_mCost/SQFT Avg Oct] > 50, Directs[_mCost/SQFT Avg Oct],
    IF(Directs[_mCost/SQFT Avg Nov] > 50, Directs[_mCost/SQFT Avg Nov],
    IF(Directs[_mCost/SQFT Avg Dec] > 50, Directs[_mCost/SQFT Avg Dec], CALCULATE(SUM(Directs[_cConstant_Zero]))))))))))))))

A circular dependency was detected: Measure: 'Directs'[_mCost/SQFT Avg Feb], Measure: 'Directs'[_mCost/SQFT First Over $50], Measure: 'Directs'[_mCost/SQFT Avg Feb].

I've done some reading up on Circular Dependency and have somewhat of an understanding how what I am trying to accomplish might confuse the engine, I'm just not sure how to resolve this.  I read the article below and tried adding a primary key/rwo number to my table, but it didn't resolve the issue.

https://www.sqlbi.com/articles/understanding-circular-dependencies/

I also read where changing nested IFs to a SWITCH statement fixed a circular dependency issue, but unfortunately, I don't think the nested IFs above can be converted to use SWITCH instead.

Any help/suggestions would be greatly appreciated.

Thank you,

@curtismob

[Anonymous]

Hi @curtismob,

Can you please share a pbix file with some sample file to test? It will be help for testing and coding formula.

In addition, you can also take a look at following link about avoiding circular dependency:

Avoiding circular dependency errors in DAX

BTW, what '_mCost/SQFT Avg XXX' means? (measure or column) If it means column, i'd like to suggest you enter to query editor to use unpivot function to transform them to reduce the complex of measure formulas.

Unpivot Data Using Excel Power Query

Regards,

Xiaoxin Sheng

[curtismob]

Xiaoxin,

Thank you for your response.  I will have to create a modified version of the pbix, which unfortunately, may take me a while to do. 

I have actually reviewed the Avoiding circular dependency errors in DAX link you referenced, I'm just not sure it applies or how to apply the suggestions.

The '_mCost/SQFT Avg XXX' are monthly measures to show the cost per sqft by month and how these may increase/decrease month over month.

I will work on getting the pbix created.

Regards,

@curtismob