Forum Discussion
Data structure
Hello! I have a table where a cost flow is going through the levels (1->2->3->4), with a few exceptions (1->2 & 1->3).
Is there a way to subordinate the table to see the flow from 4 to the source? (How much 4 earned from 3, 3 got from 2, etc.)?
The problem is that the relation between the origins and destinations is MANY to MANY.
In a decomposition tree (by level 2 detail)?
Thank you so much in advance for any ideas!
| Source level 1 | Origin level 2 | Origin of cost | Target level 1 | Target level 2 | Final cost |
| 1 | A | 150 | 2 | D | 98 |
| 1 | A | 10 | 2 | E | 7 |
| 1 | B | 50 | 2 | E | 28 |
| 1 | C | 70 | 2 | F | 42 |
| 2 | D | 98 | 3 | M | 93 |
| 2 | E | 28 | 3 | N | 23 |
| 2 | F | 42 | 3 | L | 37 |
| 1 | A | 150 | 3 | M | 42 |
| 1 | B | 50 | 3 | N | 12 |
| 1 | C | 70 | 3 | L | 18 |
| 3 | M | 130 | 4 | X | 127 |
| 3 | N | 65 | 4 | Y | 62 |
| 3 | L | 49 | 4 | Z | 46 |
4 Replies
olgadir27 - Seems like you want something that maybe uses the PATH DAX functions. Also, might be a transitive closure problem. https://community.powerbi.com/t5/Quick-Measures-Gallery/Transitive-Closure/m-p/783828#M388
olgadir27 , see if use relation can help. Make inactive relation and activate as per need
Hi olgadir27 ,
You may create calculated columns like DAX below.
Path Level1= PATH(Table1[Origin Level 1], Table1[Destination Level 1] ) Path Level2= PATH(Table1[Origin Level 2], Table1[Destination Level 2] )For reference:
Best Regards,
Amy
Community Support Team _ Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you everyone for your time and suggestions!
In PATH, I see I have duplicates both in Origin and Destination. My doubt is, can I omit them?
