Create unique ID to group

[fishboneox]

Hi,

I'm currently struggling with a perhaps easy problem but still i do.

Analysing sensor data of a moving object I am measuring the start and end event of one move. Therefore my table looking like this:

Start_Movement	End_Movement	ID
27.02.2018 09:45
27.02.2018 09:45
27.02.2018 09:45
27.02.2018 09:45
27.02.2018 09:45
27.02.2018 09:45
27.02.2018 09:45
27.02.2018 09:45
27.02.2018 09:45
27.02.2018 09:45
	27.02.2018 09:45
27.02.2018 09:46
	27.02.2018 09:46

To identify the movement later I want to create an unique ID for each movement like this:

Start_Movement	End_Movement	ID
27.02.2018 09:45		1
27.02.2018 09:45		1
27.02.2018 09:45		1
27.02.2018 09:45		1
27.02.2018 09:45		1
27.02.2018 09:45		1
27.02.2018 09:45		1
27.02.2018 09:45		1
27.02.2018 09:45		1
27.02.2018 09:45		1
	27.02.2018 09:45	1
27.02.2018 09:46		2
	27.02.2018 09:46	2

My current idea is to set a variable as ID and add +1 every time the start column and end column is blank. Using this ID I want to group the "movements" afterwards. 

The time for the start and end event are coming from an on-going time stamp every second and just in case of a movement I'm writing down the value in one of this 2 columns.

I'm not sure if this is a smart way and furthermore I'm not able to handle this idea as an DAX expression. Could someone please give its input on this thought?

[Zubair_Muhammad]

HI @fishboneox

If you Rank the Sum of Start Movement and EndMovement...you might get the ID

i.e. try this calculated colum

ID =
RANKX ( Table1, Table1[Start_Movement] + Table1[End_Movement],, ASC, DENSE )

 

[fishboneox]

Thanks for the idea. But somehow its doesn't work for me as you can see in the picture below:

I was adapting your formula as follow:

D_ID = RANKX ( Summarized; Summarized[Start_Movement] + Summarized[End_Movement];; ASC; DENSE )

Any idea why this is not matching with your results?

[Zubair_Muhammad]

@fishboneox

In your original data you only had values in one of the Columns.

The formula is just ranking the sum of Columns

[fishboneox]

You're right but even with only one data point per row it doesn't work

[Zubair_Muhammad]

Hi @fishboneox

Are you referring to the missing ranks

For example 163 should be 167...right??

Could you share your file?

[fishboneox]

I've found the problem in this case. The value was rounded. So the method with the ranking won't work in this case:

Start_Movement	End_Movement	D_ID
26.03.2018 07:04:21		152
26.03.2018 07:04:22		153
26.03.2018 07:04:23		154
26.03.2018 07:04:24		155
	26.03.2018 07:04:25	156
26.03.2018 07:04:32		163
26.03.2018 07:04:33		164
26.03.2018 07:04:34		165
26.03.2018 07:04:35		166
26.03.2018 07:04:36		167
26.03.2018 07:04:37		168
	26.03.2018 07:04:38	169
26.03.2018 07:04:46		177
	26.03.2018 07:04:47	178
26.03.2018 07:04:54		185
26.03.2018 07:04:55		186
26.03.2018 07:04:56		187

But thanks for the effort. Really appreciate it