Fix Excel Power Pivot Relationships When a Manufacturer Slicer Does Not Filter EDI
Question details
The user needs a Manufacturer slicer to filter data in both the SIOP and EDI tables in an Excel Power Pivot data model.
- Product
- Microsoft Excel Power Pivot
- Device & OS
- not provided
- Scenario
- A slicer currently uses the Manufacturer field from the SIOP table, while the report also needs to show filtered EDI results.
- Observed behavior
- Selecting a Manufacturer in the slicer does not filter the EDI table or its related PivotTable results.
Identify the common key, such as Part Number, that links SIOP and EDI. Confirm whether each part has one manufacturer or whether a part can be associated with multiple manufacturers before changing the model design.
Create a shared Part Number dimension table for SIOP and EDI
Use this recommended approach when SIOP and EDI are fact tables that must respond to the same Manufacturer slicer.
A slicer field stored in one fact table normally cannot reliably filter another fact table. Build a star schema with a unique lookup table containing one row per Part Number, then connect that lookup table to both SIOP and EDI.
If each Part Number has only one Manufacturer, store Manufacturer in the Part Number dimension table and build the slicer from that dimension field.
In Excel, create a table containing unique Part Number values and the corresponding Manufacturer values. Load it to the Data Model by selecting the table and choosing Power Pivot > Add to Data Model.
Select Power Pivot > Manage, then select Diagram View to review the tables and their existing relationships.
Create one-to-many relationships from DimPart[Part Number] to SIOP[Part Number] and from DimPart[Part Number] to EDI[Part Number]. The DimPart key must contain unique values.
In the PivotTable, choose PivotTable Analyze > Insert Slicer and select DimPart[Manufacturer], not SIOP[Manufacturer]. This allows the dimension to filter both related tables.
Use a bridge table when one part can have multiple manufacturers
Use this design when a Part Number can legitimately appear with more than one Manufacturer and the report must define how those assignments filter EDI.
Verify active relationships and slicer report connections
Use this check after correcting the model structure or when the slicer should control multiple PivotTables but affects only one.
Use WPS Office for Everyday Excel-Compatible PivotTable Reporting
Microsoft Excel Power Pivot data-model relationship issues must be corrected in Excel. For everyday spreadsheet analysis, PivotTables, filters, and Excel-compatible reporting, WPS Office is a free, lightweight alternative with a familiar interface that helps you move files and workflows smoothly.
Frequently Asked Questions
Why does a slicer from the SIOP table not filter the EDI table?
SIOP and EDI are typically separate fact tables. A field in SIOP does not provide a reliable shared filter path to EDI. Put the slicer field in a shared lookup table, such as DimPart or DimManufacturer, and relate that table to both facts.
Where should Manufacturer be stored in a Power Pivot star schema?
Store Manufacturer in the Part Number dimension when each Part Number has exactly one Manufacturer. Use DimPart[Manufacturer] for the slicer, with one-to-many relationships from DimPart to SIOP and EDI.
What if one part number has more than one manufacturer?
Create a bridge table containing the valid Part Number and Manufacturer pairs. Use separate dimension tables and test how a manufacturer selection should affect EDI records and totals before finalizing measures.
How do I connect one slicer to more than one PivotTable in Excel?
Click the slicer, go to the Slicer tab, select Report Connections, and check each PivotTable. All selected PivotTables must use the same Excel Data Model or PivotTable cache context.
How can I check Power Pivot relationships?
Open Power Pivot > Manage > Diagram View. Verify that the lookup table key is unique, key columns use compatible data types, and the lookup table connects to both SIOP and EDI through the intended columns.
