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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Multiplication with measure

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

1 ACCEPTED 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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

28 REPLIES 28
rcspaiva
Helper I
Helper I

Sorry my delay, but is this answer correct?

 

Capturar.PNG

 

 

rcspaiva
Helper I
Helper I

Hello,

 

Try use it

 

NameMeasure = sumx(table_of_calculated_column; TimeDifferenceMeasure*SpeedM/S)

 

 

Anonymous
Not applicable

Hi @rcspaiva,

 

When i try your formula it returns 0.

 

 

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... 

 

Greg_Deckler
Super User
Super User

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)

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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)
Anonymous
Not applicable

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.

Anonymous
Not applicable

Hi @bsas @rcspaiva,

 

TimeStationaryObjectSpeedMSMovingObject
10:00:00A20X
10:10:00C20X

 

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])
1.JPG

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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))

 

Capture.PNG

 

When I try to add Outputmeasure

Capture2.PNGCapture3.PNG

 

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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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)

 

TimeLapseStationaryObjectMSinceSObjectSpeedMovingObject
10:00:00X5070K1
10:10:00X10090K1
10:20:30Y30100K1
10:30:00Y7090K1
10:35:00Y110120K1
10:40:50Z2090K1
10:51:00Z70100K1

 

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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

@v-yuezhe-msft,

 

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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

@v-yuezhe-msft,

 

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])

 

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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