Forum Discussion

fishboneox's avatar
fishboneox
Frequent Visitor
8 years ago

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?

Need Help

6 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Icon for Community Champion rankCommunity Champion
    8 years ago

    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's avatar
      fishboneox
      Frequent Visitor
      8 years ago

      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's avatar
        Zubair_Muhammad
        Icon for Community Champion rankCommunity Champion
        8 years ago

        fishboneox

         

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

        The formula is just ranking the sum of Columns