topic Iterative/Recursive Calculations in DAX Commands and Tips

Iterative/Recursive Calculations

Anonymous — Mon, 20 Apr 2020 09:23:53 GMT

I have a table of products and ingredients:

Product	Ingredient	Initial price
A	D	2
A	B	0
B	C	0
C	E	2

As you can see, product A contains products D and B. D has a price value and B has no price initially. B contains C, C contains E and E has a starting value of 2. Now, looking at this you can easily tell, that the final price of product A will be 4. This is the problem I'm trying to solve using DAX formulas.

To achieve this in Excel, I add two new columns to the table:

Product	Ingredient	Initial price	Lookup value	Final price
A	D	2	= IFERROR( INDEX( TableProductsIntermediate[Price]; MATCH([@Ingredient]; TableProductsIntermediate[Product]; 0)); 0)	=[@[Initial price]] + [@[Lookup value]]
A	B	0
B	C	0
C	E	2

Here, TableProductsIntermediate is a table that contains all distinct/unique products:

Product	Price
A	=SUMIFS( TableProducts[Final price]; TableProducts[Product]; [@Product])
B
C

Excel somehow manages to run this iteratively/recursive, and gives me the following result:

Product	Ingredient	Initial price	Lookup value	Final price
A	D	2	0	2
A	B	0	2	2
B	C	0	2	2
C	E	2	0	2

Product	Price
A	4
B	2
C	2

Now, the way I've tried implementing this in DAX is as follows:

Product	Ingredient	Initial price	Lookup value	Final price
A	D	2	=LOOKUPVALUE( TableProductsIntermediate[Price]; TableProductsIntermediate[Product]; [Product])	=[Initial price] + [Lookup value]
A	B	0
B	C	0
C	E	2

Product	Price
A	=CALCULATE( sum(TableProducts[Final price]); FILTER( 'TableProducts'; 'TableProducts'[Product]='TableProductsIntermediate'[Product] ))
B
C

This throws an circular dependency error. Any ideas on how this iterative/recursive calculation can be performed in DAX?

Best,

mdhor

Re: Iterative/Recursive Calculations

Anonymous — Mon, 20 Apr 2020 09:45:34 GMT

Easiest way to do it is in Power Query or Python, not in DAX. In DAX dealing with parent-child hierarchies is awkward and the only way to handle this is to flatten the hierarchy but for that you have to know the maximum depth level in advance.

DAX has not been designed to handle such situations. Recursion is not supported. Only a version called sideways recursion is but it does not apply to your case.

PBI/DAX is not Excel.

Best
D

Re: Iterative/Recursive Calculations

Anonymous — Mon, 20 Apr 2020 10:03:51 GMT

Thank you for your answer Anonymous

How would a solution to this simple example look like in Power Query? I'm trying to avoid having to export the data and process it with other software.

Re: Iterative/Recursive Calculations

Anonymous — Mon, 20 Apr 2020 14:08:54 GMT

I'm trying to create a PQ solution but it's not going to be for the faint of heart...

Best
D

Re: Iterative/Recursive Calculations

Anonymous — Mon, 20 Apr 2020 15:10:41 GMT

Please find the PQ solution attached. When you open, please go to Power Query and have a look at the function I created to get the price of a product. The assumption is that products are in the first column and only for them do you have to calculate the total price.

Original Data is the table with your original data.

Products with final prices is the table with final prices.

getTotalProductPrice is the function that does the recursion...

You have to check this procedure against a representative number of cases because I used your data and made assumptions which might not be true about the full set. You have to TEST.

Best