Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
fishboneox
Frequent Visitor

Create unique ID to group

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_MovementEnd_MovementID
   
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_MovementEnd_MovementID
   
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:451
   
27.02.2018 09:46 2
 27.02.2018 09:462

 

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?

6 REPLIES 6
Zubair_Muhammad
Community Champion
Community Champion

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 )

 

creteID.png 


Regards
Zubair

Please try my custom visuals

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

temp_PowerBI_start_end_movement4.JPG

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?

@fishboneox

 

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

The formula is just ranking the sum of Columns


Regards
Zubair

Please try my custom visuals

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

temp_PowerBI_start_end_movement5.JPG

Hi @fishboneox

 

Are you referring to the missing ranks

 

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

 

Could you share your file?


Regards
Zubair

Please try my custom visuals

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_MovementEnd_MovementD_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:25156
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:38169
26.03.2018 07:04:46 177
 26.03.2018 07:04:47178
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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.