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.
Hi all,
I want to do a multiplication which looks like this:
TimeDifference * SpeedM/S
//
TimeDifference =Max(Table1[TIME]) - MIN(Table1[TIME]) // TIME = a Calculated column
SpeedM/S = Divide(Table1[SpeedKM/H];3,6)
Thanks in advance !
L.Meijdam
Solved! Go to Solution.
@Anonymous,
In your table, you have different SpeedM/S for different Stationary Objects. You would need to create a measure to calculate a average speed value, then calculate distance between objects.
TimeDifference = MAX(Table1[time])-MIN(Table1[time])
AverageSpeed = AVERAGE(Table1[SpeedM/S])
OutputMeasure = [TimeDifference]*[AverageSpeed]
Regards,
Lydia
Sorry my delay, but is this answer correct?
Hello,
Try use it
NameMeasure = sumx(table_of_calculated_column; TimeDifferenceMeasure*SpeedM/S)
Hello @Anonymous,
Could share some data, like three examples and the result that u are expecting.
Its going to be more easy to figure out...
Are both TimeDifference and SpeedM/S calculated columns or is SpeedM/S a measure? I'm going to assume columns. So, for measures, you need to do some sort of aggregation on columns, like:
MyMeasure = SUM(TimeDifference) * SUM(SpeedM/S)
Hi @Greg_Deckler,
I am sorry for the confusion, TimeDifference is a measure. What I meant in my post was that TimeDifference uses a Calculated column for its calculation.
SpeedM/S is a calculated column.
TimeDifference is a measure
what I want to achieve is to calculate the distance. by multiplying the time from TimeDifference with SpeedM/S
@Anonymous
In such case:
measure = [timedifference] * sum(speedM/S)
Hi @bsas,
This measure works but I am getting unexpected results from it.
For example when the time difference is 0:05:00 the number that comes out of my measure is 0,50 while the speed in that difference was around 26 Meter per second.
Am I doing something wrong ?
The thing I am trying to achieve here is to know the distance covered in that time
@Anonymous
Could you please share sample of your data table.
Time | StationaryObject | SpeedMS | MovingObject |
10:00:00 | A | 20 | X |
10:10:00 | C | 20 | X |
Timedifference represents the time between StationaryObject A and C for example. So in this case Timedifference would be 00:10:00. the expected outcome would be the distance traveled by the moving object between those points
The column "Time" we see here is a calculated column which displays an average of when the MovingObject was at StationaryObject
there is also a column with Speed in KMH "SpeedKMH" which is a normal column not a calculated one
@Anonymous,
Create a calculated column using the formula below.
Column = MINUTE(Table1[Time])*60+SECOND(Table1[Time])
Then create the following measures in your table.
TimeDifference = Max(Table1[Column])- MIN(Table1[Column])
OutputMeasure = [TimeDifference]*VALUES(Table1[SpeedMS])
Regards,
Lydia
Hi @v-yuezhe-msft @rcspaiva @bsas,
I followed the exact steps and the measure that turns time into seconds as a number works perfectly, the problem I have now is that the "outputmeasure" in @v-yuezhe-msft gives the following error when I try to display it in a visual:
"Calculation error in measure [outputmeasure]: A table of multiple values was supplied where a single value was expected"
the measure itself works but I can't get it to display something ..
Regards, L.Meijdam (excuses for the late response)
@Anonymous,
Do you create the table visual as shown in my screenshot? If not, please post the visual you create.
Regards,
Lydia
Hi @v-yuezhe-msft,
Yes I do (see pictures below)
When i add timedifference and SpeedM/S it works (although it sums SpeedM/S for every record (that is why it so high))
When I try to add Outputmeasure
Could the problem be something that the original timelapse had alot of speed records (multiple in one "TimeDifference" ?
@Anonymous,
Could you please share the complete data of your table so that I can test?
Regards,
Lydia
Hi @v-yuezhe-msft,
This is what the data of my table looks like (note speed here is still in KM/H,I use a created M/S column)
TimeLapse | StationaryObject | MSinceSObject | Speed | MovingObject |
10:00:00 | X | 50 | 70 | K1 |
10:10:00 | X | 100 | 90 | K1 |
10:20:30 | Y | 30 | 100 | K1 |
10:30:00 | Y | 70 | 90 | K1 |
10:35:00 | Y | 110 | 120 | K1 |
10:40:50 | Z | 20 | 90 | K1 |
10:51:00 | Z | 70 | 100 | K1 |
MSinceSObject represents the amount of meters since the movingobject passed the stationary object, I calculated the exact time the moving object was at the StationaryObject called "TimeAtPoint"
From that "TimeAtPoint" we wanted to know the average so that made
AverageTimeAtPoint = CALCULATE( AVERAGE(Table1[TimeAtPoint]); ALLEXCEPT(Table1;Table1[StationaryObject]) )
the last thing I want to know is the distance between the stationary points.
L.Meijdam
@Anonymous,
What formula do you use to create TimeAtPoint? Could you please post expected result based on the above complete data? Do you want to calculate the distance between different Stationary Objects or the distance between same Stationary Objects that have different TimeLapse?
Regards,
Lydia
I used this formula to create TimeAtPoint:
TimeAtPoint = Table1[TimeLapse]-Table1[TravelTimeTformat]
(traveltime is the time the movingobject took from the stationaryobject to its current location)
My expected result is to have a slicer where I can click on for example Z and X (StationaryObject) and then see the distance between those two objects in a card.
@Anonymous,
What is the TIME column you used in the following formula? If the TIME column is AverageTimeAtPoint column, please show us the data of the TravelTimeTformat column.
TimeDifference =Max(Table1[TIME]) - MIN(Table1[TIME])
Regards,
Lydia
As suggested earlier in this post I created a calculated column with the following formula:
time = MINUTE(Table1[AverageTimeAtPoint])*60+SECOND(Table1[AverageTimeAtPoint])
So in the Timedifference code that looks like this:
TimeDifference = MAX(Table1[time])-MIN(Table1[time])
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 |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
145 | |
108 | |
107 | |
89 | |
61 |