cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
shawnprodgers
Regular Visitor

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 alternatives, we look at volume (A) on a MTD and YTD basis by supplier, where the MTD/YTD dates are all dates before the date of the transaction or, alternatively, the RowIndex before the transaction's RowIndex. Then, after analyzing A, if conditions are met (B) the report will say whether the original transaction supplier was the best option or if we should have selected a different supplier (C). Because of the MTD and YTD criteria, if we suggest the supplier is different, we want to track how much volume we are changing from the original supplier to the new supplier (if C is not the original supplier, flag as changed volume (D)). To properly calculate MTD and YTD on the conditions met column (B), we then have to reference A (original volume by supplier) and D (changed volume by supplier) but we only want the totals for all rows before the current row we are trying to calculate. However, we get a circular dependency on a columnar level, because we have a calculation step that looks like A > B > C > D > B. We tried things like CALCULATE(CALCULATE(sum(Volume),Supplier=earlier(Supplier)),RowIndex<earlier(RowIndex) as well as trying to emulate Dany Hoter's LookUpValue solution, but to no avail so far. All described steps are calculated columns as analysis is done at a row (transactional) level. Any insight would be very helpful and appreciate your time,

 

Shawn

4 REPLIES 4
Eric_Zhang
Microsoft
Microsoft

@shawnprodgers

 

Your post is a little confusing, could you post any sample or upload the pbix, do mask sensitive data before uploading, you can upload it to Onedrive or any other web storage.

@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.

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

Hi Eric,

 

Thank you for your response. Attached is a model that has two tables. One is the desired outcome, the other is the same table with fact columns and calculated columns trying to get the desired outcome with the circular dependency present. The key field is the Condition Volume Total and the key ability is to be able to include the volume that has met a condition into the net total of volume in the Condition Volume Total field. We can't use measures given that each row must be analyzed individually so unless we created a report table that shows every row (which obviously isn't ideal), we want to accomplish this with calculated columns.

 

Herer is the One Drive link: https://1drv.ms/u/s!AgADk3HM2f_cki_o1vMGVr4Dk6Nh

 

Really appreciate your time,,

 

Shawn

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.