Forum Discussion

Baconbomb's avatar
Baconbomb
Icon for Helper I rankHelper I
8 years ago
Solved

Number ranges filter out singels

Im trying to solve a problem i was asked.

 

I have several excel dokuments with a bunch of data and in these documents there a number ranges.

 

 

I have 3 columns

Range from

Range to

Size of range

 

There could be 10 series in direct following and then there can be some odd numbers that dont follow

Say a range from 36000-36099 and the next one is 36100-36119 and after that there is a gap so the next number is 32235-32235

 

 

Is there some way to filter out the series that don't directly follow the serie ahead of it in the list?

 

I would like to have it in a slicer if possible so a extra coloumn with a value like 1 if its in series

 

DAX or M dont mather.

Need Help
  • ricardocamargos's avatar
    ricardocamargos
    8 years ago

    Hi  Baconbomb,

     

    I think this case has to be solved using either M and DAX, I tried to follow:

     

    M ->

            Add an index to this table;

     

    DAX ->

           I've created a calculated column to get the previous TO column value:

                 PREVIOUS_TO = SELECTCOLUMNS(FILTER(Table1; Table1[Index] = EARLIER(Table1[Index]) - 1); "To"; Table1[To])

          After that a measure calculating the gap between the previous TO with the current FROM:

                 _GAP = IF(SUM(Table1[From]) - SUM(Table1[PREVIOUS_TO]) <> 1; 1; 0)

     

    Also be aware the last range in your example was the first range (if it's ordered by FROM, it's this case you should order on M language by FROM column).

     

    I hope it helps you,

     

    Ricardo

2 Replies

  • ricardocamargos's avatar
    ricardocamargos
    Icon for Continued Contributor rankContinued Contributor
    8 years ago

    Hi  Baconbomb,

     

    I think this case has to be solved using either M and DAX, I tried to follow:

     

    M ->

            Add an index to this table;

     

    DAX ->

           I've created a calculated column to get the previous TO column value:

                 PREVIOUS_TO = SELECTCOLUMNS(FILTER(Table1; Table1[Index] = EARLIER(Table1[Index]) - 1); "To"; Table1[To])

          After that a measure calculating the gap between the previous TO with the current FROM:

                 _GAP = IF(SUM(Table1[From]) - SUM(Table1[PREVIOUS_TO]) <> 1; 1; 0)

     

    Also be aware the last range in your example was the first range (if it's ordered by FROM, it's this case you should order on M language by FROM column).

     

    I hope it helps you,

     

    Ricardo