cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper II

## 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.

1 ACCEPTED SOLUTION
Community Support

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

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
8 REPLIES 8
Community Support

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

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
Helper II

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

Helper II

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?

Super User

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?

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.
Helper II

Correct

Super User

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?

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.
Super User

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?

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.
Helper II

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors