Forum Discussion

hchauhan556's avatar
hchauhan556
Frequent Visitor
2 years ago

how to remove A circular dependency

I got the error of Circular dependency.

codes of two diff measures are. 

 
Inventory =
VAR CurrentMonthInventory = SUM(CurrentInventory[Quantity])
VAR LastMonthInventory = CALCULATE([Ending Inventory], PREVIOUSMONTH('Date'[Date]))

RETURN
IF(CurrentMonthInventory = 0, LastMonthInventory, CurrentMonthInventory)
 
other measure 
 
Ending Inventory =
VAR InvoiceQty = SUM('InvoiceVolumeReport'[Invoice Quantity])
VAR ForecastedQty = SUM('Demand'[ForecastedQty])
VAR AdjustedInventory1 =[Inventory]
VAR PackagingValue = SUM('Packaging'[Value])

RETURN
IF (
   InvoiceQty > ForecastedQty,
   AdjustedInventory1  + PackagingValue,
  AdjustedInventory1  - ForecastedQty + InvoiceQty + PackagingValue
)

ATTACHED SCREENSHOT HAS THE ERROR:


 

RELATIONS MA CREATING IS SHOWN IN AS BELOW:-

 

Need Help
Show and Tell
Tips and Tricks

3 Replies

  • danextian's avatar
    danextian
    Super User
    2 years ago

    Hi hchauhan556 ,

     

    This is where you problem lies.

    Inventory =
    VAR CurrentMonthInventory = SUM(CurrentInventory[Quantity])
    VAR LastMonthInventory = CALCULATE([Ending Inventory], PREVIOUSMONTH('Date'[Date]))

    RETURN
    IF(CurrentMonthInventory = 0, LastMonthInventory, CurrentMonthInventory)

     

     

    Your Inventory measure references Ending Inventory but Ending Inventory also references Inventory.  If you did the same thing in Excel, you'd also get a similar error.

    • hchauhan556's avatar
      hchauhan556
      Frequent Visitor
      2 years ago

      Hi Danextian

       

      both measures are mandatory and dependent on each other. how can I remove circular dependency on that point?

       

      kind regards

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi, hchauhan556 

    To remove circular dependency, you can rewrite the calculation logic of LastMonthInventory in Inventory in Ending Inventory to avoid using Inventory directly. For example, redefine and use CurrentMonthInventory in Ending Inventory instead of AdjustedInventory1. The following is a reference DAX expression:

     

    EndingInventory =
VAR InvoiceQty =
    SUM ( 'InvoiceVolumeReport'[Invoice Quantity] )
VAR ForecastedQty =
    SUM ( 'Demand'[ForecastedQty] )
VAR CurrentMonthInventory =
    SUM ( CurrentInventory[Quantity] )
VAR PackagingValue =
    SUM ( 'Packaging'[Value] )
RETURN
    IF (
        InvoiceQty > ForecastedQty,
        CurrentMonthInventory + PackagingValue,
        CurrentMonthInventory - ForecastedQty + InvoiceQty + PackagingValue
    )

     

    I hope the above DAX can be helpful to you.

     

     

     

    How to Get Your Question Answered Quickly

    If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

    Best Regards

    Jianpeng Li

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.