Need to create levels of hierarchy

[Anonymous]

Hi All,

Below is my sample data,

My table has 3 columns, No, start No and end No. Start and end No is the begining and Ending of 'No' range, but it is given at the end row of each range. I need to split this into range of hierarchy levels. Below is my expected output,

1000 - 1005 and 2000 - 2002 is the first level of hierarchy ,101-200 and 201-300 is the second level of hierarchy, 10 - 11 is the third level of hierarchy. I want to put the starting range No in the respective columns (ie...first,second,third level).

I need to calculate the last 3 columns using dax or power query.

Thanks in Advance....

[amitchandak]

@Anonymous , I think you may get first one like this

First Level =
maxx(filter(Table, [no] >= earlier([Start No]) && [no] <=earlier([end No]) && [Start No] >=1000 && [no] >=1000),[Start No])

For second and thrid you need have index and more complex calcultion. Unless data in in table format I can not try.

But I also think this not a good data to work with.

[lbendlin]

Tell whoever provided you with the source data that it is of insufficient quality. Ask them for better source data.