Skip to main content
cancel
Showing results for 
Search instead 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

Reply
User5231
Helper II
Helper II

Peg through levels

orderconverted orderserial number
calculated serial number
abukwdca11
wdcaazda 1
azdawdel 1
wdelahjt 

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
v-jayw-msft
Community Support
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_])))

vjaywmsft_0-1652683256407.png

 

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.

View solution in original post

8 REPLIES 8
v-jayw-msft
Community Support
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_])))

vjaywmsft_0-1652683256407.png

 

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.
User5231
Helper II
Helper II

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

User5231
Helper II
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?

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.

Correct

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.
danextian
Super User
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?

danextian_0-1651801159512.png

 












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.

User5231_0-1651802530917.png

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.

Helpful resources

Announcements
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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