Forum Discussion
lookup initial entry in multiple transfers
- 6 years ago
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
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