Forum Discussion

subzero355's avatar
subzero355
Frequent Visitor
4 years ago
Solved

Running sequence number based on a column

I have a calculated table with Columns A and B, what I need is Column C which is a running sequence number based on Column B. Can someone please help with the DAX formula. The series should repeat after it reaches 6 and start from 1 again.

 

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    Hi,

    Please check the below picture and the attached pbix file if it suits your requirement.

    It is for creating a new column.

     

     

    Result CC =
VAR _newtable =
    ADDCOLUMNS (
        Data,
        "@index",
            VAR _currentalphabet = Data[A]
            VAR _previousalphabet =
                MAXX ( FILTER ( Data, Data[A] < _currentalphabet ), Data[A] )
            VAR _previousnumber =
                MAXX ( FILTER ( Data, Data[A] = _previousalphabet ), Data[B] )
            RETURN
                IF ( _previousnumber <> Data[B], 1, 0 )
    )
VAR _result =
    MOD (
        SUMX ( FILTER ( _newtable, Data[A] <= EARLIER ( Data[A] ) ), [@index] ),
        6
    )
RETURN
    IF ( _result = 0, 6, _result )

1 Reply

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    4 years ago

    Hi,

    Please check the below picture and the attached pbix file if it suits your requirement.

    It is for creating a new column.

     

     

    Result CC =
VAR _newtable =
    ADDCOLUMNS (
        Data,
        "@index",
            VAR _currentalphabet = Data[A]
            VAR _previousalphabet =
                MAXX ( FILTER ( Data, Data[A] < _currentalphabet ), Data[A] )
            VAR _previousnumber =
                MAXX ( FILTER ( Data, Data[A] = _previousalphabet ), Data[B] )
            RETURN
                IF ( _previousnumber <> Data[B], 1, 0 )
    )
VAR _result =
    MOD (
        SUMX ( FILTER ( _newtable, Data[A] <= EARLIER ( Data[A] ) ), [@index] ),
        6
    )
RETURN
    IF ( _result = 0, 6, _result )