Forum Discussion
Solved
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 reques...
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 🙂
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 🙂