Forum Discussion

Esong2023's avatar
Esong2023
Frequent Visitor
2 years ago
Solved

Sequential Row Number like in Tableau

Hi there, I'm working with a table with more than 10 fields, and I'm looking to create a 'Row Num' column that shows a sequential row number (not changing when sorting by a field). Specifically, I w...
  • v-weiyan1-msft's avatar
    2 years ago

    Hi Esong2023 ,

    Based on the sample and description you provided, You may also consider try the following steps:
    1. Please try code as below to Create a Calculated table.

    Select Column = 
    DATATABLE(
        "Select Column",STRING,
        "Order",INTEGER,
        {
            {"Name",1},
            {"State",2},
            {"City",3},
            {"Enrolled Status",4},
            {"Contact for incentive",5}
        }
    )
    

    2. Use the following code to create a measure.

    M_Row Num = 
    VAR _SELECTVALUE =
        SELECTEDVALUE ( 'Select Column'[Order] )
    VAR _RANKX1 =
        RANKX (
            ALLSELECTED ( 'Table' ),
            CALCULATE ( MAX ( 'Table'[Name] ) ),
            ,
            ASC,
            DENSE
        )
    VAR _RANKX2 =
        RANKX (
            ALLSELECTED ( 'Table' ),
            CALCULATE ( MAX ( 'Table'[State] ) ),
            ,
            ASC,
            DENSE
        )
    VAR _RANKX3 =
        RANKX (
            ALLSELECTED ( 'Table' ),
            CALCULATE ( MAX ( 'Table'[City] ) ),
            ,
            ASC,
            DENSE
        )
    VAR _RANKX4 =
        RANKX (
            ALLSELECTED ( 'Table' ),
            CALCULATE ( MAX ( 'Table'[Enrolled Status] ) ),
            ,
            ASC,
            DENSE
        )
    VAR _RANKX5 =
        RANKX (
            ALLSELECTED ( 'Table' ),
            CALCULATE ( MAX ( 'Table'[Contact for incentive] ) ),
            ,
            ASC,
            DENSE
        )
    RETURN
        SWITCH (
            _SELECTVALUE,
            1, _RANKX1,
            2, _RANKX2,
            3, _RANKX3,
            4, _RANKX4,
            5, _RANKX5,
            _RANKX1
        )
    

    3. Put the corresponding field into the visual object and do the following.

    Result is as below.

     

    Best Regards,
    Yulia Yan

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.