Add suffix based on count of occurrence of value in column to create unique identifier

[IvanS]

Hi all,

I am currently working on migrating the database to Power BI and I am struggling to create unique identifier (column Id) which is adding 1,2,3 ... as suffix based on count of occurrence of value in column "Point". In old database this is handled in Microsoft Visual Studio with following Java script:

Output1Buffer.ConnectorId = int.Parse(point.id.ToString() + connectorCount.ToString());

The result is following:

Any idea how to create calculated column in Power BI directly and store it as number?

Thank you

Ivan

[parry2k · Accepted solution]

@IvanS you can easily achieve this by ranking, check this post How to use RANKX in DAX (Part 1 of 3 - Calculated Columns) - RADACAD

✨ Follow us on LinkedIn and  to our YouTube channel

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to 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.

[IvanS]

Thank you @parry2k  for pointing me to the correct direction 🙂

I had to create Index column starting with 1 and then create column with RANKX with index column included.

Unique ID = 
'Table'[data.id] 
& 
RANKX(
    FILTER('Table', 
    'Table'[data.id]=EARLIER('Table'[data.id])
    ),
 'Table'[index],,ASC)

Result is following (fyi: RANK column consists of RANKX function only):

Way how to create index column (do not forget to hide this column in report view):

Ivan