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
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?
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 )
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?
In your original data you only had values in one of the Columns.
The formula is just ranking the sum of Columns
You're right but even with only one data point per row it doesn't work
Hi @fishboneox
Are you referring to the missing ranks
For example 163 should be 167...right??
Could you share your file?
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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
113 | |
105 | |
95 | |
58 |
User | Count |
---|---|
175 | |
147 | |
134 | |
105 | |
82 |