Forum Discussion

shawnprodgers's avatar
shawnprodgers
Regular Visitor
10 years ago

Circular Dependency - Only calculate rows before current row

Hello Power BI Community,   I have an model where we are trying to calculate contract optimization where we take all transactions and compare them against alternatives. When analyzing these alterna...
Need Help
Eric_Zhang's avatar
Eric_Zhang
Icon for Microsoft Employee rankMicrosoft Employee
10 years ago

shawnprodgers

 

Thanks for sharing the pbix. The formula looks good. Not sure what did you do, however when I create a new column with the exactly same formula, the circular dependency error doesn't occur and the output seems good.

 

For readability, you can format the the DAX formular on DAX formatter.

Condition Volume Total =
[Volume]
    + CALCULATE (
        SUM ( Test1[Volume] ),
        FILTER (
            ALL ( Test1 ),
            AND (
                Test1[Contract] = EARLIER ( Test1[Contract] ),
                Test1[Index.1] < EARLIER ( Test1[Index.1] )
            )
        )
    )
    + CALCULATE (
        CALCULATE ( SUM ( Test1[Qualified Volume] ), Test1[Condition Positive] = "Yes" ),
        FILTER ( ALL ( Test1 ), Test1[Index.1] < EARLIER ( Test1[Index.1] ) )
    )

 

Understanding Circular Dependencies for your reference.

shawnprodgers's avatar
shawnprodgers
Regular Visitor
10 years ago

Hi Eric,

 

Thanks for taking a look. I agree the formula works as is. The central issue I have is I need to reference the total of the formula in the Condition Volume Total column so when I do the Condition Test formula, it is calculating on the desired volume total. Even if I create a new column with the stated functioning formula, when I try to reference is in the Conditional Volume Total column, the circular reference returns and I'm back to square one. Any ideas?

 

Appreciate your time,

 

Shawn