i trying build excel based database tracking of equipment supplier delivery point. although excel 2013 has solved majority of issues powerpivot , data models, of relationships aren't registering.
design
basically, have following tabs , 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 unique identifier (identifier) allows tracking purposes. delivery point looks equipment , can track using id, can purchasing. remaining information required contracts , installation.
packing - equipment 'packed' boxes, pallets, or other freight containers. separate equipment table multiple pieces of equipment can 'packed' freight containers, or total number of pieces of equipment can split across several freight containers.
freight - spreadsheet tracks 'freight containers', boxes, pallets, or containers. required when freight in storage or in transit, identify looks , assist in locating equipment.
problem:
the relationships aren't working in powerpivot. have many 1 relationship between packing , equipment, , packing , freight, cannot packing pivot table recognise these relationships, so, when create pivot table based on packing, , try feed in 1 of other columns either equipment or freight, adds information, rather specific information.
question:
is structure not effective relationship? building pivot-table incorrectly?
previous searches
i've looked on stackoverflow, , there things seem similar seem complicated question. powerpivot relationships not working - multiple tables (relates creating unique id's, have these, not applicable) laravel 4.2 sync multidimensional array in pivot table (unfortunately don't know or use vba, confuses me think it's designing seems simple problem) https://stackoverflow.com/questions/25913739/three-tables-in-pivot-laravel (this appears answer question, couldn't work, , lavarel again beyond capabilities)
i don't mind learning new things, if vba or otherwise answer, i'll learn! however, seems simple function can done in excel , i've missed crucial. prefer keep database simple possible, it's less break!
i'd appreciate help. thanks!
do relationships in powerpivot this:
..where identifier in packaging table linked identifier in equipment table , packing id in packing table linked packing id in freight table?
if so, model structure fine long add fields equipment , freight tables pivot. don't need vba or laravel, may need dax measures results want show correctly on table. can give more information expect see?
edit: answer expanded take account of further information
ok, illustrate how works, i've made data. didn't know "package no" column for, i've left blank.
if create pivot table using packing id freight table , identifier equipment table, this:
this showing every combination of packing id , identifier behaviour saw , not you're after, wait! happens if create measure performs sort of calculation on packing table:
[rows in packaging table]:=countrows('packing') then add values in pivot:
looks promising. how text values equipment fields on table?
[description]:=firstnonblank(equipment[equipment description],1) gives:
right, there 2 problems this. firstly rows back, secondly totals showing values don't want. let's add new measure takes care of these (note 2 parts of , statement perform checks these 2 conditions).
[description crossmatch]:=if(and([rows in packaging table]>0,countrows(values(equipment[equipment description]))=1),[description],blank()) and result is:
the same approach can used pull of fields equipment table pivot. fields freight table can added directly without requiring formula.
Comments
Post a Comment