Forum Discussion

Dorinb's avatar
Dorinb
Frequent Visitor
6 years ago
Solved

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...
  • v-lili6-msft's avatar
    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