Dynamic index column based on alphabetical order or values of a column (name)

[BalazsNy]

Hi All,

I have a report which contains a table including different employee data & I would like to add a dynamic index column based on the alphabetical order of emlpoyee names. However, I would like the index column to be dynamic based on all the selected filters so that it would always start from 1 & not keeping the original (unfiltered) index number.

Example:

Could you advice any solution / DAX formula to solve this issue?

Thanks in advance.

[ABA007 · Accepted solution]

Hi, 
I suggest that you use a measure for that : 
1- First create a Mesure 
Dynamic Index = 
RANKX(
ALLSELECTED(Employee),

Employee[name], ASC,DENSE

)

DENSE here ensure no gap between numbers.
2- Add it to your table visual

3- sort by Dynamic Index

Hope this would help

Best! 

[v-ssriganesh]

Hello @BalazsNy, 

Could you please confirm if your query has been resolved by the provided solutions? This would be helpful for other members who may encounter similar issues.

Thank you for being part of the Microsoft Fabric Community.

[v-ssriganesh]

Hello @BalazsNy,

Hope everything’s going great with you. Just checking in has the issue been resolved or are you still running into problems? Sharing an update can really help others facing the same thing.

Thank you.

[v-ssriganesh]

Hello @BalazsNy,

We hope you're doing well. Could you please confirm whether your issue has been resolved or if you're still facing challenges? Your update will be valuable to the community and may assist others with similar concerns.

Thank you.

[v-ssriganesh]

Hello @BalazsNy,
Thank you for reaching out to the Microsoft Fabric Community Forum.

I have reproduced your scenario using a sample employee table and got the expected output using a DAX measure. The dynamic index resets based on filters and orders alphabetically by employee name.

Here’s the DAX measure I used:

Dynamic Index =

VAR CurrentEmployee = SELECTEDVALUE(Employees[employee])

RETURN

    RANKX(

        FILTER(

            ALLSELECTED(Employees),

            NOT(ISBLANK(Employees[employee]))

        ),

        Employees[employee],

        CurrentEmployee,

        ASC,

        DENSE

    )

Please ensure you place this in a Table visual and sort by employee ascending. Add slicers like market and status to dynamically control your view.

For your reference, I’ve attached a sample .pbix file and thanks @MFelix & @ABA007 for sharing your valuable insights.

Best Regards,
Ganesh Singamshetty.

[MFelix · Accepted solution]

Hi @BalazsNy ,

Currently the best option is to use visual calculations follow these steps:

• On your table click the New Visual calculation button

• Add a field called ID = 1

• Add a second field running some of ID (rename it to whatever you want)

What is good about this approach is that the order is always the same no matter what column you select to sort your values

• Hide the ID column

Check documentation below:

https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-visual-calculations-overview

[ABA007 · Accepted solution]

Hi, 
I suggest that you use a measure for that : 
1- First create a Mesure 
Dynamic Index = 
RANKX(
ALLSELECTED(Employee),

Employee[name], ASC,DENSE

)

DENSE here ensure no gap between numbers.
2- Add it to your table visual

3- sort by Dynamic Index

Hope this would help

Best!