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 want the 'Row Num' to display from 1 to the last row number regardless of the sorting field.

Could you please guide me on how to achieve this? Any insights or suggestions on the best approach would be greatly appreciated.

Thanks in advance for your assistance!

 

Here are examples.

When sorting by Name:

Row NumNameStateCityEnrolled StatusContact for incentive
1CarlCAOrangeYesYes
2EllenTXAustinNoNo
3RayPAPhilaPendingYes

 

Now, when sorting by State:

Row NumNameStateCityEnrolled StatusContact for incentive
1CarlCAOrangeYesYes
2RayPAPhilaPendingYes
3EllenTXAustinNoNo

 

Now, when sorting by Enrolled Status: 

Row NumNameStateCityEnrolled StatusContact for incentive
1EllenTXAustinNoNo
2RayPAPhilaPendingYes
3CarlCAOrangeYesYes
Need Help
  • v-weiyan1-msft's avatar
    v-weiyan1-msft
    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.

4 Replies

  • v-weiyan1-msft's avatar
    v-weiyan1-msft
    Community Support
    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.

    • Esong2023's avatar
      Esong2023
      Frequent Visitor
      2 years ago

      Thank you very much for your response!!