topic Re: Create a column based on index value in another column in DAX Commands and Tips

Create a column based on index value in another column

Anonymous — Tue, 31 May 2022 09:19:40 GMT

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.

Number	order	Next_number
1,32	1	2,3
2,3	2	0.9
0,9	3	0,5
0	4	0,5
0	5	0,5
0,5	6	69
69	7

Please l need help.

Re: Create a column based on index value in another column

Jihwan_Kim — Tue, 31 May 2022 09:41:28 GMT

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] )

Re: Create a column based on index value in another column

Anonymous — Tue, 31 May 2022 10:35:15 GMT

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.

Re: Create a column based on index value in another column

Jihwan_Kim — Tue, 31 May 2022 10:45:48 GMT

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] )

Re: Create a column based on index value in another column

Anonymous — Tue, 31 May 2022 15:05:34 GMT

Thank you very much Jihwan_Kim 😁. It works.