n/a

Step 1: Single Date Dimension Table

1. Create a Single Date Dimension Table:
• Ensure you have a single date dimension table that includes all the necessary date attributes (Year, Month, Week, Day, etc.).
• Remove any additional date tables and replace them with this single date table.
2. Connect the Date Table:
• Connect the date table to your fact tables (Starts, Ships, Completes, and Forecasted Data) on the relevant date columns (e.g., StartDate, ShipDate, CompleteDate, etc.).

Step 2: Single Part Number Dimension Table

1. Create a Single Part Number Dimension Table:
• Ensure you have a single part number dimension table (BOM) that includes all the necessary part number attributes.
• This table should contain unique part numbers.
2. Connect the Part Number Table:
• Connect the part number table to your fact tables on the relevant part number columns (e.g., Ship PN, Commit PN, PN).

Step 3: Relationship Management

1. Remove Ambiguous Relationships:
• Remove any relationships that are causing ambiguity. You should have a clear, single path from each fact table to the date and part number dimension tables.
2. Active Relationships:
• Ensure the relationships from the Date table to the fact tables are active.
• Ensure the relationships from the BOM table to the fact tables are active.

Step 4: Create Relationship Tables (Bridge Tables)

1. Create Bridge Tables if Necessary:
• If you still encounter ambiguity, consider creating bridge tables. For example, a bridge table for Part Numbers if the fact tables have multiple columns for part numbers that need to be unified.

Step 5: Use DAX Measures

1. Create DAX Measures:
• Use DAX to create measures that accurately calculate the metrics you need (e.g., total commits, total ships, total completes).
• These measures can then be used in your visuals.

Example Relationships and DAX Measures

Relationships

• Date[Date] → Starts[StartDate]
• Date[Date] → Ships[ShipDate]
• Date[Date] → Completes[CompleteDate]
• Date[Date] → ForecastedData[ForecastDate]
• BOM[PartNumber] → Starts[Commit PN]
• BOM[PartNumber] → Ships[Ship PN]
• BOM[PartNumber] → Completes[Complete PN]
• BOM[PartNumber] → ForecastedData[PN]

DAX Measures

Total Commits = SUM('Starts'[Quantity])

Total Ships = SUM('Ships'[Quantity])

Total Completes = SUM('Completes'[Quantity])

Total Forecasted = SUM('ForecastedData'[Quantity])

Step 6: Validate Your Model

1. Check Relationships:
• Ensure there are no many-to-many relationships unless necessary.
• Validate that slicers and filters work as expected without causing ambiguity.
2. Test the Model:
• Test with different slicers (e.g., by date, part number) to ensure the data displays correctly across different time frames and part numbers.

By following these steps, you should be able to create a robust Power BI model that correctly handles dates and part numbers, eliminating ambiguous paths and ensuring your data is displayed accurately across different slicers and filters.