Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hey all,
I have the following data structure:
ID | TotalDistance | StartTime | EndTime |
1 | 1000 | 10:00 | |
1 | 1100 | ||
1 | 1300 | ||
1 | 1400 | 12:00 | |
1 | 1400 | 12:20 | |
2 | 2000 | 14:00 | |
2 | 2200 | ||
2 | 2300 | 14:30 | |
2 | 2400 | 15:00 | |
2 | 2400 | 15:10 | |
3 | 500 | 9:00 | |
3 | 550 | ||
3 | 600 | 9:20 | |
3 | 600 | 9:25 | |
3 | 630 | ||
3 | 650 | 9:50 |
Now, I want to calculate the distance travelled between each start and end time for each ID. How would I go about doing this? If possible in a measure!
Ralph
Solved! Go to Solution.
Hi @RalphO
Here is how you can do it with a calculated column. I have attached a PBIX file
Column = VAR StartTotal = MAXX( FILTER( 'Table1', Table1[ID] = EARLIER('Table1'[ID]) && 'Table1'[TotalDistance] < EARLIER('Table1'[TotalDistance]) && NOT ISBLANK('Table1'[StartTime]) ),[TotalDistance]) RETURN IF( NOT ISBLANK('Table1'[EndTime]), 'Table1'[TotalDistance] - StartTotal )
HI @RalphO
Can you please provide what your expected output would be for that sample set of data. This will help clarify your requirement.
Cheers,
Phil
I should look something like this (A TripDistance value for the other rows should also be fine):
ID | TotalDistance | StartTime | EndTime | TripDistance |
1 | 1000 | 10:00 | ||
1 | 1100 | |||
1 | 1300 | |||
1 | 1400 | 12:00 | 400 | |
1 | 1400 | 12:20 | ||
2 | 2000 | 14:00 | ||
2 | 2200 | |||
2 | 2300 | 14:30 | 300 | |
2 | 2400 | 15:00 | ||
2 | 2450 | 15:10 | 50 | |
3 | 500 | 9:00 | ||
3 | 550 | |||
3 | 600 | 9:20 | 100 | |
3 | 600 | 9:25 | ||
3 | 630 | |||
3 | 650 | 9:50 | 50 |
Basically I want the total distance travelled in each trip, with trip defined as the time between starttime and endtime.
Hi @RalphO
Here is how you can do it with a calculated column. I have attached a PBIX file
Column = VAR StartTotal = MAXX( FILTER( 'Table1', Table1[ID] = EARLIER('Table1'[ID]) && 'Table1'[TotalDistance] < EARLIER('Table1'[TotalDistance]) && NOT ISBLANK('Table1'[StartTime]) ),[TotalDistance]) RETURN IF( NOT ISBLANK('Table1'[EndTime]), 'Table1'[TotalDistance] - StartTotal )
@Phil_Seamark That works, thank you!
For bonus points: do you think it would also be possible to do this with a measure instead of a calculated column?
Hi @RalphO
Yes this would be possible, however a calculated measure needs to know what fields you are using in an axis to help ensure you get the right values.
Would you be using a Table/Matrix visual with the measure?
@Phil_Seamark I will probably be using a line graph visual for it, with timestamps as the x-axis (one stamp for every 2 seconds, not included in the sample data). Would a measure work for this, or solely for an actual matrix/table?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |