Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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
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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
24 | |
20 | |
15 | |
13 |
User | Count |
---|---|
51 | |
46 | |
39 | |
19 | |
19 |