Peg through levels

[User5231]

order	converted order	serial number	calculated serial number
abuk	wdca	1	1
wdca	azda		1
azda	wdel		1
wdel	ahjt		1

I'm trying to find a way to calculate the calculated column, which traces the serial number through the orders based on the associated converted order.

I know how I could do this by explicitly filtering down levels it's certain amount of time until I run out of orders to peg, but I'd like to know a way to dynamically do this not knowing how many levels you might have to go to get to the final converted order and return the original serial number. 

Basically I am trying to track the order changing but keep the serial number as the common thread.

I'm open to ideas.

[v-jayw-msft · Accepted solution]

Hi @User5231 ,

Please check the formulas.

Parent_ = LOOKUPVALUE('Table'[converted order],'Table'[converted order],'Table'[order])

path_ = PATH('Table'[converted order],'Table'[Parent_])&"|"

group_ = 
var position = FIND("|",'Table'[path_])
return
LEFT('Table'[path_],position-1)

calculated serial number = CALCULATE(SUM('Table'[serial number]),FILTER('Table','Table'[group_]=EARLIER('Table'[group_])))

Best Regards,

Jay

[User5231]

I think the solution here will be using the path functions. I'll test it out and see.

[User5231]

Sorry, let me explaing.

Abuk became wdca and was serial number 1.

wdca because azda then wdel then ajht. 

How do you step through that without explictly loking up the value and calling out each new level as a coumn or stored variable. Can it be looped?

[danextian]

abuk became wdca
wdca became azda
azda became wdel
wdel becahme ahjt

their calculated serial number is 1 because the initital serial number prior to further conversion was 1. Is my understanding correct?

[User5231]

Correct

[danextian]

Do you have timestamp column and other helper columns that will indicate that they belong to the same group? Up to how many levels can the conversion get to?

[danextian]

Hi @User5231 ,

If I understood correctly, your logic is to use converted order as the lookup value, search for it in the order column and return the serial number from the same row. But looking at the sample data and using the aforementioned logic, converted order - wdca will return blank/null as the serial number from the same row where order is wdca is blank -  your calculated serial number all shows 1. Can you please elaborate your logic further or if you were to do it in Excel, what would your formula be?

[User5231]

In excel I would just keep looking up the previous lookup. I could do that in power BI to, but, would like a cleaner solution.