Create a Version Number

[Till]

Hi All,
I would like to create the Version column using a Measure or Calculated column. The newest version should always be number 1. It can happen that several versions are loaded during the day. With Max I would always get the latest version but I need an automatic numbering.
Thanks for suggestions

[Anonymous · Accepted solution]

Hi @Till ,

Accoring to your screenshot, are you want to add independent index for each location?

If so, you could try using RANKX() function.

https://docs.microsoft.com/en-us/dax/rankx-function-dax 

However, Sorting text data with RANKX() function may not be so accurate. So you will need to convert the text column to DATETIME type.

_datetime = DATE(MID('Table'[s_insert_ts],7,4),MID('Table'[s_insert_ts],4,2),MID('Table'[s_insert_ts],1,2))+TIMEVALUE(RIGHT('Table'[s_insert_ts],5))

Then create a rank column as below.

Column = RANKX(FILTER('Table','Table'[location]=EARLIER('Table'[location])),'Table'[_datetime],,DESC)

Best Regards,

Jay

[Anonymous · Accepted solution]

Hi @Till ,

Accoring to your screenshot, are you want to add independent index for each location?

If so, you could try using RANKX() function.

https://docs.microsoft.com/en-us/dax/rankx-function-dax 

However, Sorting text data with RANKX() function may not be so accurate. So you will need to convert the text column to DATETIME type.

_datetime = DATE(MID('Table'[s_insert_ts],7,4),MID('Table'[s_insert_ts],4,2),MID('Table'[s_insert_ts],1,2))+TIMEVALUE(RIGHT('Table'[s_insert_ts],5))

Then create a rank column as below.

Column = RANKX(FILTER('Table','Table'[location]=EARLIER('Table'[location])),'Table'[_datetime],,DESC)

Best Regards,

Jay

[Till · Accepted solution]

HI 

v-jayw-msft

PREVIEW

thank you, you have helped me a lot.

It works

@AllisonKennedy 
Thank you also for your help

Have a nice week.

Till

[AllisonKennedy]

@Till  Can you use Power Query? If so, sort the s_insert_ts column first Descending, then click Add Column > Index > From 1

[Till]

Hi Allison,
thank you for your message.
Yes, that would work.
However, today I have the challenge that one more site is added. Then this does not work. Do you also have a solution for this?

[AllisonKennedy]

@Till 

Sorry, I don't understand your question - is the screenshot what actually happened or what you want to happen? Is the raw data coming from different files?

[Till]

Hi Allison,

It is one table where the Location column has been added. These locations can make uploads in the table, which means that they are attached to the back of the table.
KR and thank's for your help.
Till