Adding new column to table to give counter of no of records.

[AOD]

Hi All, 

Is there a way to add new column to table which gives counter of the records in table.

Eg as below 

Thanks.

AOD

Employee	Field 1	Modified date	New column to be added
EP123	Yes	22-Jun-20	1
EP123	No	23-Jun-20	2
EP123	Yes	24-Jun-20	3
EP124	Yes	22-Jun-20	1
EP124	No	23-Jun-20	2
EP125	Yes	21-Jun-20	1
EP125	No	22-Jun-20	2
EP125	Yes	23-Jun-20	3
EP125	No	24-Jun-20	4

[v-shex-msft · Accepted solution]

Hi @AOD,

You can try to use below calculated column formula with countrows and earlier functions to calculate the index based on employee group and date order:

Index =
CALCULATE (
    COUNTROWS ( table ),
    FILTER (
        ALL ( table ),
        [Employee] = EARLIER ( table[Employee] )
            && [Modified date] <= EARLIER ( table[Modified] )
    )
)

Regards,

Xiaoxin Sheng

[parry2k]

@AOD yes you can use RANKX function to achieve the, see links below

https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡