Recursive graph calculation in Dax

Question

Hi,&nbsp;I have solved the following problem in power query but are looking for an alternative and improved performance solution using DAX.&nbsp;It is effectively a recursive graph solution and required to perfrom an costing solution.&nbsp;I have a setup of Input Costs:&nbsp;CostPoolInput CostCostPool 10&nbsp;100CostPool 11&nbsp;150&nbsp;The following relationship table exists:SrcCostPool&nbsp;SrcLayer&nbsp;DestCostPool&nbsp;DestLayerAllocation FactorRelationshipCostPool 10&nbsp;L1CostPool 21&nbsp;L20.333333CostPool 10-CostPool 21CostPool 10&nbsp;L1CostPool 22&nbsp;L20.666667CostPool 10-CostPool 22CostPool 11&nbsp;L1CostPool 31&nbsp;L31CostPool 11-CostPool 31CostPool 21&nbsp;L2CostPool 31&nbsp;L30.272727CostPool 21-CostPool 31CostPool 21&nbsp;L2CostPool 32&nbsp;L30.727273CostPool 21-CostPool 32CostPool 22&nbsp;L2CostPool 31&nbsp;L30.993084CostPool 22-CostPool 31CostPool 22&nbsp;L2CostPool 32&nbsp;L30.006916CostPool 22-CostPool 32CostPool 31&nbsp;L3CostPool 41&nbsp;L40.193614CostPool 31-CostPool 41CostPool 31&nbsp;L3CostPool 43&nbsp;L40.805477CostPool 31-CostPool 43CostPool 31&nbsp;L3CostPool 51&nbsp;L50.000267CostPool 31-CostPool 51CostPool 31&nbsp;L3CostPool 52&nbsp;L50.000642CostPool 31-CostPool 52CostPool 32&nbsp;L3CostPool 41&nbsp;L40.278034CostPool 32-CostPool 41CostPool 32&nbsp;L3CostPool 43&nbsp;L40.385561CostPool 32-CostPool 43CostPool 32&nbsp;L3CostPool 42&nbsp;L40.336406CostPool 32-CostPool 42CostPool 41&nbsp;L4CostPool 52&nbsp;L50.033149CostPool 41-CostPool 52CostPool 41&nbsp;L4CostPool 53&nbsp;L50.966851CostPool 41-CostPool 53CostPool 42&nbsp;L4CostPool 52&nbsp;L50.041096CostPool 42-CostPool 52CostPool 42&nbsp;L4CostPool 53&nbsp;L50.958904CostPool 42-CostPool 53CostPool 43&nbsp;L4CostPool 52&nbsp;L50.023904CostPool 43-CostPool 52CostPool 43L4CostPool 53&nbsp;L50.976096CostPool 43-CostPool 53&nbsp;The relationship is effectively a layer graph type model where a layer of CostPool nodes have a dependancy on a previous layer/s of nodes. For exampleCost of CostPool 21 = Input Cost 10 * Allocation Factor (CostPool 10-Costpool 21),&nbsp;CostPool 31 = Input Cost 11 * Allocation Factor (CostPool 11-CostPool31) + Cost of CostPool 21 * Allocation Factor (CostPool 21-CostPool 31)&nbsp;This will be repeated until the last layer. For the example it will be all the CostPool nodes in L5.&nbsp;&nbsp;Can someone maybe help with a solution in DAX?&nbsp;Many thanks&nbsp;&nbsp;

greg_deckler · Answer

tonie_tollig&nbsp;Not 100% certain what you are going for here but recursion is not generally possible in Power BI. I have done extension research in this area and never found a good way.&nbsp;Runge-Kutta and the Limits of DAX - Microsoft Power BI Community

tonie_tollig · Answer

Thanks GregThat is not the best news but at least I know not to spend to much more time on DAX while we wait for the implementation of PREVIOUSVALUE in DAX

Forum Discussion

Recursive graph calculation in Dax

2 Replies

Related content

recursive calculation

Recursion Strategy

Recursive calculation

Calculate Recursively (?)

Recursive Calculation and Forecast measure

SrcCostPool	SrcLayer	DestCostPool	DestLayer	Allocation Factor	Relationship
CostPool 10	L1	CostPool 21	L2	0.333333	CostPool 10-CostPool 21
CostPool 10	L1	CostPool 22	L2	0.666667	CostPool 10-CostPool 22
CostPool 11	L1	CostPool 31	L3	1	CostPool 11-CostPool 31
CostPool 21	L2	CostPool 31	L3	0.272727	CostPool 21-CostPool 31
CostPool 21	L2	CostPool 32	L3	0.727273	CostPool 21-CostPool 32
CostPool 22	L2	CostPool 31	L3	0.993084	CostPool 22-CostPool 31
CostPool 22	L2	CostPool 32	L3	0.006916	CostPool 22-CostPool 32
CostPool 31	L3	CostPool 41	L4	0.193614	CostPool 31-CostPool 41
CostPool 31	L3	CostPool 43	L4	0.805477	CostPool 31-CostPool 43
CostPool 31	L3	CostPool 51	L5	0.000267	CostPool 31-CostPool 51
CostPool 31	L3	CostPool 52	L5	0.000642	CostPool 31-CostPool 52
CostPool 32	L3	CostPool 41	L4	0.278034	CostPool 32-CostPool 41
CostPool 32	L3	CostPool 43	L4	0.385561	CostPool 32-CostPool 43
CostPool 32	L3	CostPool 42	L4	0.336406	CostPool 32-CostPool 42
CostPool 41	L4	CostPool 52	L5	0.033149	CostPool 41-CostPool 52
CostPool 41	L4	CostPool 53	L5	0.966851	CostPool 41-CostPool 53
CostPool 42	L4	CostPool 52	L5	0.041096	CostPool 42-CostPool 52
CostPool 42	L4	CostPool 53	L5	0.958904	CostPool 42-CostPool 53
CostPool 43	L4	CostPool 52	L5	0.023904	CostPool 43-CostPool 52
CostPool 43	L4	CostPool 53	L5	0.976096	CostPool 43-CostPool 53