Table relationships

[Misca]

I'm quite confident with Excel but a complete newbie when it comes to BI and now I'd like to add the BI tools to my set of skills as well. I can understand the basic relationships between the tables but I'm lost when I have more than a single column in my facts table that are all related to the same column in another table. And since I'm working with transportation data there are lots of that kind fields in my facts tables:

My facts table (=Shipments) has fields like ID, PickUpDate, OriginZip, ConsignorID, DeliveryDate, DestinationZip, ConsigneeID, Weight and PayerID.

Then there are several dimension tables that are easily related like ZipCodes-table and Terminals-table. Each ZipCode in the ZipCodes-table is related to a single Terminal in the Terminals table.

With Excel it's really easy to use VLOOKUP to get the Origin Terminal for the OriginZip and the Destination Terminal for the DestinationZip but with PowerPivot I can only get the relationship to work from one Zip to the ZipCodes table. How can I get both terminals to work? I've tried USERELATIONSHIP and LOOKUPVALUE but I just can't get them to work.

Once I can get both Terminals for each shipments begins the next problem:

I need to calculate the number of shipments & tons between the terminals. In a grid that's really easy to do in Pivot Table but what I really want is only a single column for the Terminal name and then three other columns for the Inbound, Outbound and Internal measures where the Inbound and Outbound numbers are quite easily calculated as the same number of shipments is leaving from this terminal and arriving to another terminal but the internal numbers (ie. Origin Terminal = Destination Terminal) should be divided in half in the calculation since it's the same shipment both ways. How could I do that with Power Pivot?

When I'm using normal Pivot Tables I'm using GetPivotData -function to get the numbers to my Terminals table and then use Rank and Large -functions to get the numbers in my actual reports.

I'm thankful for every little bit of help I can possibly get here: I'm dreaming with Power Pivot I might be able to do the same reports easier and without the helper columns & VLookups & even show the terminal volumes on a PowerMap instead of just old fashioned charts. Also, I'm hoping I can use the same method to combine the Consignor, Consignee, and PayerIDs to my Customers table but I'm afraid that might not be possible because there are lots of Consignees that are not found in the Customers table at all.

 

[ImkeF]

Hi Misca,
with USERRELATIONSHIP you should get what you need here. Sth like: CALCULATE(SUM(tons), USERRELATIONSHIP(Facts[DestinationZip], ZipCodes[Zip])).

But it is crucial, that you only take your measures from your fact table - nothing else! All fields that go into rows, columns, filters and slicers should come from the Dim-Tables!! (A good practice anyway)

Wrap this in sth like: IF(MAX(Facts[OrigignZip])=MAX(Facts[DestinationZip]), Measure/2, Measure) in order to handle the internals.

hth, else ask
Imke

 

[Misca]

Thanks! I'll try that first thing in the morning. Looks like I was too stuck in the past / good old Excel and wanted to get the Terminal names to my facts table so that I could filter the data to see the shipment streams better (=where the shipments come from to this terminal and where they go ie. how many trucks are needed for each route and so on).