Data structure

[olgadir27]

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

[v-xicai]

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:

PATH, PATHLENGTH, PATHITEM, PATHITEMREVERSE, PATHCONTAINS – playing whith hierarchies (DAX – Power P...

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.

[olgadir27]

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?

[amitchandak]

@olgadir27 , see if use relation can help. Make inactive relation and activate as per need

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in...

[Greg_Deckler]

@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