Search code examples
database-designexcel-2013powerpivotdatamodel

Excel 2013 PowerPivot - 3 Tables, relationships not registering

I am trying to build an excel based database for tracking of equipment from supplier to delivery point. Although Excel 2013 has solved the majority of my issues with PowerPivot and the Data Models, however some of my relationships aren't registering.

Design

Basically, I have the following tabs and information:

Table: Equipment

Columns: Identifier (Unique), Package No, Equipment Description, Area

Table: Packing

Columns:Identifier, Packing ID

Table: Freight

Columns: Packing ID, Packing Type, Dimension

Reasoning:

Equipment - Each piece of equipment has a unique identifier (Identifier) that allows for tracking purposes. The delivery point looks for equipment and can track using this ID, as can purchasing. The remaining information is required for contracts and for installation.

Packing - Equipment is 'packed' into boxes, pallets, or other freight containers. This is separate to the Equipment table as multiple pieces of equipment can be 'packed' into freight containers, or a total number of pieces of equipment can be split across several freight containers.

Freight - This is the spreadsheet that tracks the 'freight containers', be it boxes, pallets, or containers. This is required for when the freight is in storage or in transit, to easily identify what it looks like and to assist in locating equipment.

Problem:

The relationships aren't working in the PowerPivot. I have a many to one relationship between Packing and Equipment, and Packing and Freight, but I cannot get the Packing pivot table to recognise these relationships, so, when I create a pivot table based on Packing, and try to feed in one of the other columns from either Equipment or Freight, it just adds all the information, rather than the specific information.

Question:

Is my structure not effective for a relationship? Am I building the pivot-table incorrectly?

Previous Searches

I've looked on Stackoverflow, and there are some things that seem similar but seem to complicated for this question. PowerPivot Relationships Not Working - Multiple Tables (relates to creating unique ID's, but I already have these, so not applicable) Laravel 4.2 Sync multidimensional array in pivot table (unfortunately I don't know or use VBA, so this confuses me but I think it's too much designing for what seems to be a simple problem) https://stackoverflow.com/questions/25913739/three-tables-in-pivot-laravel (this appears to answer the question, but I couldn't get it to work, and Lavarel is again beyond my capabilities)

I don't mind learning new things, so if VBA or otherwise is the only answer, I'll just learn! However, this seems like a simple function that can be done in excel and that I've just missed something crucial. I prefer to keep the database as simple as possible, it's less likely to break!

I'd really appreciate your help. Thanks!

Solution

  • Do your relationships in PowerPivot look like this:

    enter image description here

    ..where the Identifier in the Packaging table is linked to the Identifier in the Equipment table and the Packing ID in the Packing Table is linked to the Packing ID in the Freight table?

    If so, then the model structure is fine as long as you add the fields from the Equipment and Freight tables to your Pivot. You don't need any VBA or Laravel, but you may need some DAX measures to get the results you want to show correctly on the table. Can you give more information about exactly what you expect to see?

    Edit: Answer expanded to take account of further information

    Ok, to illustrate how this works, I've made up some data. I didn't know what your "Package No" column is for, so I've left it blank.

    enter image description here

    If I create a pivot table using the Packing ID from the Freight table and the Identifier from the Equipment table, then I get this:

    enter image description here

    This is showing every combination of Packing ID and Identifier which is the behaviour you saw and not what you're after, but wait! What happens if I create a measure that performs some sort of calculation on the packing table:

    [Rows in Packaging Table]:=COUNTROWS('Packing')

    Then add this to the Values in the Pivot:

    enter image description here

    Looks promising. But how do we get the text values for the equipment fields on the table?

    [Description]:=FIRSTNONBLANK(Equipment[Equipment Description],1)

    Gives:

    enter image description here

    Right, there are two problems with this. Firstly all the rows are back, secondly the totals are showing values which we don't want. Let's add a new measure that takes care of these (note the two parts of the AND statement perform the checks for these two conditions).

    [Description CrossMatch]:=IF(AND([Rows in Packaging Table]>0,COUNTROWS(VALUES(Equipment[Equipment Description]))=1),[Description],BLANK())

    And the result is:

    enter image description here

    The same approach can be used to pull any of the fields from the equipment table into the pivot. Fields from the freight table can just be added directly without requiring any formula.