Forum Discussion
Iterative/Recursive Calculations
Hi
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
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
D
4 Replies
- 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