Forum Discussion

CarlsBerg999's avatar
CarlsBerg999
Helper V
3 years ago

Circular dependency

Hi, 

 

I have a situation with a pretty obvious circularity. My data contains 3 productions where the output is always used in the next production. We first make production number 1. and then utilize the output in production 2. Then we take output of production 2 and utilize it in production 3. 

 

I have production costs developing in "Production Cost per Output Unit" and I use this column to calculate an "Adjusted production cost", which should be used in the next production's "Production Cost per Output Unit". And there we have it: a circular problem. 

 

Any ideas on how to get around the circularity problem here? When the entire column is looked at, we do have a circularity problem, but in fact the cost of ID 832 should only affect the cost of ID 834.   

 

 

 

Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi CarlsBerg999 ,

     

    Dax is not good at handling recursive questions, Please check the post below to see if you can derive the formula further.

    Recursion in DAX

     

    Or if possible, consider performing the calculation in the data source or in the PQ.

     

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly -- How to provide sample data

  • MarkLaf's avatar
    MarkLaf
    Super User
    3 years ago

    Can you please provide sample tables and a description or pseudocode of what measure or other output you are looking for?

    Shot in the dark, but I suspect you could take an approach similar to parent-child hierarchy to get what you want.

    Another approach I've used before is to use a separate table to define relationship between rows in your table, set up inactive relationships, and then build measures using 'Original Table' --Activated Physical Relationship--> 'Relationship Table' --Virtual Relationship--> 'Original Table'

    Another option (probably the better option if possible) would be to remodel your data. Something like

    Production Step Dimension

    Id (PK) Output Cost
    1 $500
    2 $400
    3 $450
    4 $600

    Production Process Dimension

    Id (PK) Name
    1 Process A
    2 Process B

    Full Production Fact

    Production Process Id (FK) Sequence Production Step (FK) Volume
    1 1 2 4
    1 2 3 5
    1 3 1 5
    2 1 1 3
    2 2 4 6

    Then, you could do a measure like

     

     

    Total Cost = 
SUMX( 
    'Full Production Fact', 
    RELATED( 'Production Step Dimension'[Output Cost] ) 
      * 'Full Production Fact'[Volume] 
)