One to Many Relationship

[kalesb]

Hi,

I'm new to Powerpivot. I want to get information from the Parts Table and Kit Table to build a Kit Cost Report/Listing. The Parts Table contains: Part Number, Unit of Issue, Vendor Code, SMR Code, Federal Class Code. The Kit Table contains Kit No., Part Number, and Nomenclature. I set a relationship between Kit Table and Part Table (Part Table = the lookup table). The Kit Table has a many to one relationship to the Parts Table or it has many Kit part Numbers to one Part Number record from the Parts Table. I wanted to be a display in excel (spreadsheet normal columns) format.

I thought I could do this by putting Kit Number and Nomenclature in the Rows label. I built a Measure for Unit of Issue = Calculate(Kit Table[Unit of Issue], Filter(Parts Table, Parts Table[Part Number]=(Kit Table[Part Number]))). But got Error msg= out of context. Can I build this with PowerPivot giving me a Complete Kit list: Kit Number, Part Number, Nomenclature, Unit of Issue, Vendor Code, SMR Code, Federal Class Code? Thank you for any assistance provided.

 

[kalesb]

Hi,

I'm new to Powerpivot. I want to get information from the Parts Table and Kit Table to build a Kit Cost Report/Listing. The Parts Table contains: Part Number, Unit of Issue, Vendor Code, SMR Code, Federal Class Code. The Kit Table contains Kit No., Part Number, and Nomenclature. I set a relationship between Kit Table and Part Table (Part Table = the lookup table). The Kit Table has a many to one relationship to the Parts Table or it has many Kit part Numbers to one Part Number record from the Parts Table. I wanted to be a display in excel (spreadsheet straight columns) format.

I thought I could do this by putting Kit Number and Nomenclature in the Rows label. I built a Measure for Unit of Issue = Calculate(Kit Table[Unit of Issue], Filter(Parts Table, Parts Table[Part Number]=(Kit Table[Part Number]))). But got Error msg= "The value for column 'PartNum' in table 'ABOM' cannot be determined in the current context. Check that all columns referenced in the calculation expression exist, and that there are no circular dependencies. This can also occur when the formula for a measure refers directly to a column without performing any aggregation--such as sum, average, or count--on that column. The column does not have a single value; it has many values, one for each row of the table, and no row has been specified.". Can I build this with PowerPivot giving me a Complete Kit list: Kit Number, Part Number, Nomenclature, Unit of Issue, Vendor Code, SMR Code, Federal Class Code? Thank you for any assistance provided.

List would look like this:

Kit Number Part Number Nomenclature Unit of Issue Vendor Code SMR Code Federal Class Code
Kit001 P0001 Cap 1 9501 PAZ01 ERB
Kit001 P0002 Top 1 9503 hAZ01 SRB
Kit002 P0001 Cap 1 9501 PAZ01 ERB
Kit003 P0001 Cap 1 9501 PAZ01 ERB