Circular dependency

[CarlsBerg999]

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.   

[MarkLaf]

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] 
)

[Anonymous]

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