lookup initial entry in multiple transfers

[Dorinb]

Hi,

I have two columns with Entry no and Transferred from entry no and would need to calculate a column Expected initial Entry no, that brings the initial entry no if an item has transferred ( it's differnt than zero).

It is easy to bring the value if we have one transfer for example entry 2 but I couldnt find a solution where you have multiple transfers. Eg. entry no 5, the initial entry no is 1.

Any help would be much appreciated.

[v-lili6-msft · Accepted solution]

hi, @Dorinb 

Based on my research, you could try this way to get it:

Step1:

In Edit Queries, repleace "0" with null for [Transferred from entry no] column.

Step2:

Create a new calculate column for Entry No which is not transformmed.

WithoutTransferred = IF('Table'[Transferred from entry no]=BLANK(),'Table'[Entry no])

Step3:

Then use this formula to create the result column:

initial entry = 
VAR InitialentryNo=PATHITEM(PATH('Table'[Entry no],'Table'[Transferred from entry no]),1) return
LOOKUPVALUE('Table'[WithoutTransferred],'Table'[Entry no],VALUE(InitialentryNo))

Result:

By the way, I changed the last row data for test.

and here is sample pbix file, please try it.

Regards,

Lin