Forum Discussion

Helper I

4 years ago

Solved

create index column in DAX

Hi, I have a column name Employee ID, and endofmonth column that goes like this:

EmpID EndofMonth

19000 09/08/2019

19000 10/08/2019

19001 09/08/2019

19001 10/08/2019

19001 11/08/2019

19002 12/08/2019

I would like to create an index column like this:

EmpID EndofMonth Index

19000 09/08/2019 1

19000 10/08/2019 2

19001 09/08/2019 3

19001 10/08/2019 4

19001 11/08/2019 5

19002 12/08/2019 6.

Thank you for your help

Tips and Tricks

v-luwang-msft
4 years ago
Hi ivannye ,

Try to use the following dax to create a new column:

index = RANKX('Table', RANKX('Table','Table'[EmpID],,ASC,Dense)+0.01*RANKX('Table','Table'[EndofMonth],,ASC,Dense),,ASC,Dense)

Final output:

Did I answer your question? Mark my post as a solution!

Best Regards

Lucien
create index column in DAX.pbix37 KB

4 Replies

Samarth_18
Community Champion
4 years ago
Hi ivannye
you can directly create a index column from Transform Data tab. PFB screenshot for your reference:-

Thanks,
Samarth
- ivannye
  Helper I
  4 years ago
  Hi,
  
  I already know but it returns a duplicate value when I do it. I would like to do it using dax.
  - Samarth_18
    Community Champion
    4 years ago
    Hi ivannye
    
    Could you please share with example what you want and what is the output you are getting?
    
    Thanks,
    Samarth
v-luwang-msft
Community Support
4 years ago
Hi ivannye ,

Try to use the following dax to create a new column:

index = RANKX('Table', RANKX('Table','Table'[EmpID],,ASC,Dense)+0.01*RANKX('Table','Table'[EndofMonth],,ASC,Dense),,ASC,Dense)

Final output:

Did I answer your question? Mark my post as a solution!

Best Regards

Lucien
create index column in DAX.pbix37 KB