Forum Discussion
lookup initial entry in multiple transfers
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.
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
1 Reply
v-lili6-msftCommunity Support
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