Vlookup in Powerpivot?

[cajste]

Hi!

I have to tables, one voyage table containing all the voyages our different trucks has done with departure and arrival time for each voyage and the other table containing GPS data taken every 64 seconds for each truck, example below.

What I want to know is what ListItemID each GPS point is refereable to? I would like to have the In excel I would use a vlookup but how do I do that in powerpivot?

Brgds,
Caj

[TABLE="width: 697"]
<tbody>[TR]
[TD]ListItemID[/TD]
[TD]Vehicle[/TD]
[TD]RealDeparture[/TD]
[TD]RealArrival[/TD]
[/TR]
[TR]
[TD]9177abe0-9c38-4baa-af2f-7cc6eb063ba9[/TD]
[TD]142.001[/TD]
[TD="align: right"]2012-12-01 13:56[/TD]
[TD="align: right"]2012-12-01 14:16[/TD]
[/TR]
[TR]
[TD]ab3c3ae7-eeff-4600-a3fb-173e4cfef706[/TD]
[TD]142.001[/TD]
[TD="align: right"]2012-12-01 14:14[/TD]
[TD="align: right"]2012-12-02 00:43[/TD]
[/TR]
[TR]
[TD]4f41977e-6f19-4efc-ac46-3946c3bdfa17[/TD]
[TD]142.001[/TD]
[TD="align: right"]2012-12-02 04:38[/TD]
[TD="align: right"]2012-12-02 06:44[/TD]
[/TR]
[TR]
[TD]bf05b049-6805-4fc4-b909-ffa91e08ca36[/TD]
[TD]142.001[/TD]
[TD="align: right"]2012-12-02 17:35[/TD]
[TD="align: right"]2012-12-02 17:48[/TD]
[/TR]
[TR]
[TD]bf05b049-6805-4fc4-b909-ffa91e08ca37[/TD]
[TD]142.002[/TD]
[TD="align: right"]2012-12-02 19:35[/TD]
[TD="align: right"]2012-12-02 23:48[/TD]
[/TR]
</tbody><colgroup><col><col><col><col></colgroup>[/TABLE]

[TABLE="width: 283"]
<tbody>[TR]
[TD]Vehicle[/TD]
[TD]GPS TIME[/TD]
[/TR]
[TR]
[TD]142.001[/TD]
[TD="align: right"]2012-12-01 14:02:21[/TD]
[/TR]
[TR]
[TD]142.001[/TD]
[TD="align: right"]2012-12-01 14:02:25[/TD]
[/TR]
[TR]
[TD]142.001[/TD]
[TD="align: right"]2012-12-01 14:02:29[/TD]
[/TR]
[TR]
[TD]142.001[/TD]
[TD="align: right"]2012-12-01 14:02:33[/TD]
[/TR]
[TR]
[TD]142.001[/TD]
[TD="align: right"]2012-12-01 14:02:37[/TD]
[/TR]
[TR]
[TD]142.001[/TD]
[TD="align: right"]2012-12-01 14:02:41[/TD]
[/TR]
[TR]
[TD]142.001[/TD]
[TD="align: right"]2012-12-01 14:02:45[/TD]
[/TR]
[TR]
[TD]142.001[/TD]
[TD="align: right"]2012-12-01 14:02:49[/TD]
[/TR]
[TR]
[TD]142.001[/TD]
[TD="align: right"]2012-12-01 14:02:53[/TD]
[/TR]
[TR]
[TD]142.001[/TD]
[TD="align: right"]2012-12-01 14:02:57[/TD]
[/TR]
[TR]
[TD]142.001[/TD]
[TD="align: right"]2012-12-01 14:03:01[/TD]
[/TR]
[TR]
[TD]142.001[/TD]
[TD="align: right"]2012-12-01 14:03:05[/TD]
[/TR]
[TR]
[TD]142.001[/TD]
[TD="align: right"]2012-12-01 14:03:09[/TD]
[/TR]
[TR]
[TD]142.001[/TD]
[TD="align: right"]2012-12-01 14:03:13[/TD]
[/TR]
[TR]
[TD]142.001[/TD]
[TD="align: right"]2012-12-01 14:03:17[/TD]
[/TR]
[TR]
[TD]142.001[/TD]
[TD="align: right"]2012-12-01 14:03:21[/TD]
[/TR]
[TR]
[TD]142.001[/TD]
[TD="align: right"]2012-12-01 14:03:25[/TD]
[/TR]
[TR]
[TD]142.001[/TD]
[TD="align: right"]2012-12-01 14:03:29[/TD]
[/TR]
</tbody>[/TABLE]

 

[MD610]

This is a typical banding scenerio using a disconnected table with a minor variation to account for various vehicles.

See aritcle below for fundamentals:

Alberto Ferrari : Banding with PowerPivot

For your situation you have to check if the GPS time falls within the ListItem time range and if the vehicle matches.

Your data sample was not good for testing because all GPS records you provided only fall within one ListItem range, plus you only provided GPS data for 1 vehicle. I added some additional records of my own to test.

First, you need a unique ID for the GPS records to use in your final pivot. You might actually have this in your data and just didn't provide it with your sample. I used a calculated column with just a concatenation of Vehicle and GPS TIME.

Then you just need one measure:


[ListItemBand]:=CALCULATE(VALUES('ListItem'
[ListItemID]),FILTER('ListItem', MAX('GPSData'[GPS TIME])<= 'ListItem'[RealArrival] && MAX('GPSData'[GPS TIME])>='ListItem'[RealDeparture] && 'ListItem'[Vehicle]=MAX('GPSData'[Vehicle])))

Use the GPS ID in your Pivot Row Labels and this measure in your values. I think this should work.

 

[cajste]

Thanks, will try ASAP!

 

[cajste]

I've been looking at this and I'm not sure if your suggestion solves the problem. The examples above are only parts of the actual tables, the first table also contains customerID, routeID, amount of cargo, from destination and final destination etc., and the second table also contains data on fuel consumption, altitude, longitud etc.

What I would like to do is to create a link between the two tables so that I can group/filter on specific routes, customers etc. As far as I understand it the way to do this is to find the ListItemID for each row in the Vehicle/GPS TIME based on the vehicle and GPS TIME in the second table and the intervall between Real departure and Real Arrival for each vehicle in the first table. When that is done I can use =Relate() to get Customers, Route etc. into the GPS data table.

I'm new to PowerPivot so this might be a very bad way of doing this, I appriciate any help!

//Caj

 

[cajste]

I think I'm beginning to understand this, but I have a problem with the formula. It looks like below and does not return anything and gives no error indication. Have I done anything wrong?

//Caj

=CALCULATE(VALUES(V_LocoUsage[Loco2ListItemID]);FILTER(V_LocoUsage;MAX('MGW DATA'[EVENT TIME])<=V_LocoUsage[RealArrival]&&MAX('MGW DATA'[EVENT TIME])>=V_LocoUsage[RealDeparture]&&V_LocoUsage[LocoNo2]=MAX('MGW DATA'[Loco2])))

 

[MD610]

I think maybe I misunderstood what you needed since I didn't realize all the other fields involved.

What about modifying the formula above a bit and using it as a Calculated Column in your GPS data instead of a measure? You could basically make a calculated column for every field you want in the GPS data. Modifying my original equation, it would be:
=CALCULATE(VALUES('ListItem'

[ListItemID]),FILTER('ListItem', 'GPSData'[GPS TIME]<= 'ListItem'[RealArrival] && 'GPSData'[GPS TIME]>='ListItem'[RealDeparture] && 'ListItem'[Vehicle]='GPSData'[Vehicle]))

This would bring the appropriate ListItemID into your GPS table.

You can then make another Calculated Column and change the VALUES() field to another field from ListItems to add that column to GPS data. So CustomerID would be:

=CALCULATE(VALUES('ListItem'[CustomerID]),FILTER('ListItem', 'GPSData'[GPS TIME]<= 'ListItem'[RealArrival] && 'GPSData'[GPS TIME]>='ListItem'[RealDeparture] && 'ListItem'[Vehicle]='GPSData'[Vehicle]))

 

[cajste]

Thank you so much! And sorry for not mentioning all the other columns from the beginning.

I tried using it as a calculated column but then realized that the database in i bad condition with for example overlaping time intervals per vehicle, and the formula returns errors because of that. Haven't figured out how to handle that yet.

If I understand correctly, your solutions covers the case where the start and end date for each row is in different columns. Is there a similar way to handle a case where there is only a column with start dates for each row, like for example with the two tabels below? I would like to plot consumption per km with speed on the X-axis. Is it possible?

Brgds,
Caj



[TABLE="width: 864"]
<tbody>[TR]
[TD]Table1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Table2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Vehicle
[/TD]
[TD]Date
[/TD]
[TD]Fuel Consumption
[/TD]
[TD][/TD]
[TD]Vehicle
[/TD]
[TD]Date
[/TD]
[TD]Distance
[/TD]
[TD]Duration (h)
[/TD]
[TD]Speed
[/TD]
[/TR]
[TR]
[TD="align: right"]1
[/TD]
[TD="align: right"]2012-12-01 13:50
[/TD]
[TD="align: right"]3
[/TD]
[TD][/TD]
[TD]142.001
[/TD]
[TD="align: right"]2012-12-01 13:57
[/TD]
[TD="align: right"]0,276
[/TD]
[TD="align: right"]0,018
[/TD]
[TD="align: right"]16
[/TD]
[/TR]
[TR]
[TD="align: right"]1
[/TD]
[TD="align: right"]2012-12-01 13:55
[/TD]
[TD="align: right"]1
[/TD]
[TD][/TD]
[TD]142.001
[/TD]
[TD="align: right"]2012-12-01 13:58
[/TD]
[TD="align: right"]0,415
[/TD]
[TD="align: right"]0,018
[/TD]
[TD="align: right"]23
[/TD]
[/TR]
[TR]
[TD="align: right"]1
[/TD]
[TD="align: right"]2012-12-01 14:00
[/TD]
[TD="align: right"]43
[/TD]
[TD][/TD]
[TD]142.001
[/TD]
[TD="align: right"]2012-12-01 13:59
[/TD]
[TD="align: right"]0,590
[/TD]
[TD="align: right"]0,018
[/TD]
[TD="align: right"]33
[/TD]
[/TR]
[TR]
[TD="align: right"]1
[/TD]
[TD="align: right"]2012-12-01 14:05
[/TD]
[TD="align: right"]44
[/TD]
[TD][/TD]
[TD]142.001
[/TD]
[TD="align: right"]2012-12-01 14:00
[/TD]
[TD="align: right"]0,575
[/TD]
[TD="align: right"]0,018
[/TD]
[TD="align: right"]32
[/TD]
[/TR]
[TR]
[TD="align: right"]1
[/TD]
[TD="align: right"]2012-12-01 14:10
[/TD]
[TD="align: right"]13
[/TD]
[TD][/TD]
[TD]142.001
[/TD]
[TD="align: right"]2012-12-01 14:01
[/TD]
[TD="align: right"]0,573
[/TD]
[TD="align: right"]0,018
[/TD]
[TD="align: right"]32
[/TD]
[/TR]
[TR]
[TD="align: right"]1
[/TD]
[TD="align: right"]2012-12-01 14:15
[/TD]
[TD="align: right"]43
[/TD]
[TD][/TD]
[TD]142.001
[/TD]
[TD="align: right"]2012-12-01 14:02
[/TD]
[TD="align: right"]0,535
[/TD]
[TD="align: right"]0,018
[/TD]
[TD="align: right"]30
[/TD]
[/TR]
[TR]
[TD="align: right"]1
[/TD]
[TD="align: right"]2012-12-01 14:20
[/TD]
[TD="align: right"]197
[/TD]
[TD][/TD]
[TD]142.001
[/TD]
[TD="align: right"]2012-12-01 14:04
[/TD]
[TD="align: right"]0,545
[/TD]
[TD="align: right"]0,018
[/TD]
[TD="align: right"]31
[/TD]
[/TR]
[TR]
[TD="align: right"]1
[/TD]
[TD="align: right"]2012-12-01 14:25
[/TD]
[TD="align: right"]134
[/TD]
[TD][/TD]
[TD]142.001
[/TD]
[TD="align: right"]2012-12-01 14:05
[/TD]
[TD="align: right"]0,552
[/TD]
[TD="align: right"]0,018
[/TD]
[TD="align: right"]31
[/TD]
[/TR]
[TR]
[TD="align: right"]1
[/TD]
[TD="align: right"]2012-12-01 14:30
[/TD]
[TD="align: right"]130
[/TD]
[TD][/TD]
[TD]142.001
[/TD]
[TD="align: right"]2012-12-01 14:06
[/TD]
[TD="align: right"]0,568
[/TD]
[TD="align: right"]0,018
[/TD]
[TD="align: right"]32
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]142.001
[/TD]
[TD="align: right"]2012-12-01 14:07
[/TD]
[TD="align: right"]0,557
[/TD]
[TD="align: right"]0,018
[/TD]
[TD="align: right"]31
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]142.001
[/TD]
[TD="align: right"]2012-12-01 14:08
[/TD]
[TD="align: right"]0,498
[/TD]
[TD="align: right"]0,018
[/TD]
[TD="align: right"]28
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]142.001
[/TD]
[TD="align: right"]2012-12-01 14:09
[/TD]
[TD="align: right"]0,430
[/TD]
[TD="align: right"]0,018
[/TD]
[TD="align: right"]24
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]142.001
[/TD]
[TD="align: right"]2012-12-01 14:10
[/TD]
[TD="align: right"]0,430
[/TD]
[TD="align: right"]0,018
[/TD]
[TD="align: right"]24
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]142.001
[/TD]
[TD="align: right"]2012-12-01 14:11
[/TD]
[TD="align: right"]0,419
[/TD]
[TD="align: right"]0,018
[/TD]
[TD="align: right"]24
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]142.001
[/TD]
[TD="align: right"]2012-12-01 14:12
[/TD]
[TD="align: right"]0,494
[/TD]
[TD="align: right"]0,018
[/TD]
[TD="align: right"]28
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]142.001
[/TD]
[TD="align: right"]2012-12-01 14:13
[/TD]
[TD="align: right"]0,307
[/TD]
[TD="align: right"]0,018
[/TD]
[TD="align: right"]17
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]142.001
[/TD]
[TD="align: right"]2012-12-01 14:14
[/TD]
[TD="align: right"]0,169
[/TD]
[TD="align: right"]0,018
[/TD]
[TD="align: right"]9
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]142.001
[/TD]
[TD="align: right"]2012-12-01 14:15
[/TD]
[TD="align: right"]0,600
[/TD]
[TD="align: right"]0,018
[/TD]
[TD="align: right"]34
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]142.001
[/TD]
[TD="align: right"]2012-12-01 14:16
[/TD]
[TD="align: right"]1,103
[/TD]
[TD="align: right"]0,018
[/TD]
[TD="align: right"]62
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]142.001
[/TD]
[TD="align: right"]2012-12-01 14:17
[/TD]
[TD="align: right"]1,459
[/TD]
[TD="align: right"]0,018
[/TD]
[TD="align: right"]82
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]142.001
[/TD]
[TD="align: right"]2012-12-01 14:18
[/TD]
[TD="align: right"]1,712
[/TD]
[TD="align: right"]0,018
[/TD]
[TD="align: right"]96
[/TD]
[/TR]
</tbody>[/TABLE]

 

[cajste]

Sorry for all the qeustions...

I cleaned the data and got the calculated column to work nicely. However, when I try to create a relationsship between the two columns I get an error message saying that a circular dependency was discovered. The point of the banding was to be able to relate the two tables to each each others.

Also tried to create a third table with that column with relations to the two the tables and avoid a direct relationsship between the two, but gor the same error. o

Any suggestions on how to solve this?

Brgds,
Caj

 

[MD610]

Do you still need the relationship if you bring all the fields into one table using Calculated Columns? Use the new Calculated columns for rows and slicers.

 

[cajste]

Of course, I should have seen that one, thanks!

Once again, thanks alot for the help, made a huge difference!

//Caj