Forum Discussion
Peg through levels
| 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.
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
8 Replies
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?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.
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?
I think the solution here will be using the path functions. I'll test it out and see.
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
