Slicer Selection To Sequence(-s)

I recently came across a similar pattern, transforming a selection of numbers into a text of numbers sequences.

I found the pattern intriguing, partly because I had been solving a similar patterns in SQL working as a data warehouse engineer. And there I knew a ‘special trick’ or optimization technique to solve this kind of problem in linear time.

I don’t really know how this problem is named in mathematics (in mathematics everything has a name) is named but I can show you the algorithm.

First, laying the ground work, we have a slicer a number sequence for the user to select, that can be created by DAX:

Parameter = GENERATESERIES ( 1, 50, 1 )

Secondly, we explain the algorithm that will be used in the DAX calculation.

Or better, show you. To put in simple terms, it enumerates all the values, first, second, enumerates the selected values and computes the difference. Because the difference then is the same for all groups of selected items that do not have non-selected items in between them. In this way, we just group by the difference and select the minimum and maximum value from the groups and, voila, we are left with sequences. In PBI we then just concatenate the sequences in a single string and show the value in the report/dashboard (DAX code below).

All Sequences =
VAR _All =
    ADDCOLUMNS (
        ALL ( Parameter[Parameter] ),
        “Rank_All”, RANKX ( ALL ( Parameter ), Parameter[Parameter],, asc )
    )
VAR _Selected =
    ADDCOLUMNS (
        ALLSELECTED ( Parameter[Parameter] ),
        “Rank_Selected”, RANKX ( Parameter, Parameter[Parameter],, asc )
    )
VAR joined =
    ADDCOLUMNS (
        NATURALINNERJOIN ( _All, _Selected),
        “Groups”, [Rank_All] – [Rank_Selected]
    )
VAR groups =
    GROUPBY (
        joined,
        [Groups],
        “Min”, MINX ( CURRENTGROUP (), [Parameter] ),
        “Max”, MAXX ( CURRENTGROUP (), [Parameter] )
    )
RETURN
    CONCATENATEX ( groups, IF ( [Min] = [Max], [Min], [Min] & “-“ & [Max] ), “. “ )

The DAX code is computed in steps.

1. Rank (enumerate) all the available numbers
2. Rank (enumerate) the selected values
3. Join the table together with an inner join (leave the non-selected items out)7
4. Group by the difference in enumerations (groups to sequences), and select the min and max number of the group (to show the sequence)
5. Concatenate the sequences with a comma in a single string
6. Done.
   * (insert the calculation in a visual)

You can download and play with the file here.