Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Create a column based on index value in another column

l have a table which contains 2 columns: number and  order. l need a column "next_number" . l want to have the value of next row of the column number sort  by order. when the next number is null, we take the next number not null. 

NumberorderNext_number
1,3212,3
2,320.9
0,930,5
040,5
050,5
0,5669
697 

 

Please l need help.

 

  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    Hi,

    Thank you for your feedback, and sorry that I missed your requirement.

    Could you please check if the below and the attached pibx file are providing a correct result? 

     

     

     

    Next number CC =
VAR nextorder =
    MINX (
        FILTER ( Data, Data[order] > EARLIER ( Data[order] ) && Data[Number] <> 0 ),
        Data[order]
    )
RETURN
    MAXX ( FILTER ( Data, Data[order] = nextorder ), Data[Number] )

     

4 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    4 years ago

    Hi,

    Please check the below picture and the attached pbix file.

    It is for creating a new column.

     

     

    Next number CC =
VAR nextorder =
    MINX ( FILTER ( Data, Data[order] > EARLIER ( Data[order] ) ), Data[order] )
RETURN
    MAXX ( FILTER ( Data, Data[order] = nextorder ), Data[Number] )
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    it gives the value of next row.  but when the next value of number is "0" it is supposed to skip it bcause it is null and use the next value of number not null.

    • Jihwan_Kim's avatar
      Jihwan_Kim
      Icon for Super User rankSuper User
      4 years ago

      Hi,

      Thank you for your feedback, and sorry that I missed your requirement.

      Could you please check if the below and the attached pibx file are providing a correct result? 

       

       

       

      Next number CC =
VAR nextorder =
    MINX (
        FILTER ( Data, Data[order] > EARLIER ( Data[order] ) && Data[Number] <> 0 ),
        Data[order]
    )
RETURN
    MAXX ( FILTER ( Data, Data[order] = nextorder ), Data[Number] )

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Thank you very much Jihwan_Kim 😁. It works.