Forum Discussion
DAX: Avoid cyclic dependency
Hi all,
I am new to power BI.
I am trying to calculate the daily open pieces.
I am able to do this in excel, but when copieng to PBI I get a cyclic dependency.
Pieces requested = sum of all open pieces on a certain requested dispatch date
Pieces Shipped = sum of all pieces shipped on a certain loaded dispatch date
Backlog Formula = first row // =B3-D3 and from there on =C4-D4
Pieces requested +Backlog = first row // =B3 and from there on =B4+E3
I have tried working with offset, but only get a cyclic dependency.
Is there a solution doing it in this logic?
Much appreciate your help.
BR
Hi Jaed ,
For this you need to create two different measures:
Pieces Requested + Backlog = VAR temptable = FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Date] < MAX ( 'Table'[Date] ) ) RETURN SUM ( 'Table'[Pieces Requested] ) + SUMX ( temptable, 'Table'[Pieces Requested] - 'Table'[Pieces Shiped] ) Backlog = VAR temptable = FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Date] <= MAX ( 'Table'[Date] ) ) RETURN SUMX ( temptable, 'Table'[Pieces Requested] - 'Table'[Pieces Shiped] )If you want to have this based on a column redo the formulas to:
Backlog Column = VAR temptable = FILTER ( 'Table', 'Table'[Date] <= EARLIER( 'Table'[Date] ) ) RETURN SUMX ( temptable, 'Table'[Pieces Requested] - 'Table'[Pieces Shiped] ) Pieces Requested + Backlog Column = VAR temptable = FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Date] < EARLIER( 'Table'[Date] ) ) RETURN 'Table'[Pieces Requested] + SUMX ( temptable, 'Table'[Pieces Requested] - 'Table'[Pieces Shiped] )Thank you very much 🙂
2 Replies
Hi Jaed ,
For this you need to create two different measures:
Pieces Requested + Backlog = VAR temptable = FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Date] < MAX ( 'Table'[Date] ) ) RETURN SUM ( 'Table'[Pieces Requested] ) + SUMX ( temptable, 'Table'[Pieces Requested] - 'Table'[Pieces Shiped] ) Backlog = VAR temptable = FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Date] <= MAX ( 'Table'[Date] ) ) RETURN SUMX ( temptable, 'Table'[Pieces Requested] - 'Table'[Pieces Shiped] )If you want to have this based on a column redo the formulas to:
Backlog Column = VAR temptable = FILTER ( 'Table', 'Table'[Date] <= EARLIER( 'Table'[Date] ) ) RETURN SUMX ( temptable, 'Table'[Pieces Requested] - 'Table'[Pieces Shiped] ) Pieces Requested + Backlog Column = VAR temptable = FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Date] < EARLIER( 'Table'[Date] ) ) RETURN 'Table'[Pieces Requested] + SUMX ( temptable, 'Table'[Pieces Requested] - 'Table'[Pieces Shiped] )Thank you very much 🙂