Trying to visualize production data tables in Power BI

Hi dticetek ,

You can achieve this in Power BI using a combination of DAX and Power Query without needing to merge all tables permanently. Your data model consists of a Header Table, which contains serial numbers and general product information, and multiple Process Tables, which store details about each step in the production process. The key relationship between these tables is the Serial Number, which serves as a unique identifier linking them.

In Power BI’s Model View, ensure that each Process Table has a Many-to-One relationship with the Header Table using the Serial Number as the key. This will allow you to filter the process data dynamically based on the selected serial number. If the process tables share a similar structure, you can append them into a single Process Master Table using Power Query. This involves using the Append Queries feature to combine all process tables, adding a new column to indicate the process type (e.g., “Welding,” “Assembly”), and ensuring all necessary columns are retained. This unified process table will make it easier to analyze production flow.

Once the data model is structured, create a Serial Number Search Report in Power BI. Use a Slicer (Dropdown or Text Filter) to allow users to input a Serial Number. Then, add a Table Visual or Matrix Visual displaying the process history for the selected serial number, including fields such as Process Step Name, Start Time, End Time, Operator, and Status. Sorting this table by the Start Time in ascending order will ensure a chronological view of how the product was manufactured.

To calculate the elapsed time between production steps, you can create a DAX measure that compares the current step’s start time with the previous step’s end time:

Elapsed Time (Minutes) = 
VAR CurrentStepTime = SELECTEDVALUE(ProcessTable[Start Time])
VAR PreviousStepTime = CALCULATE(
    MAX(ProcessTable[End Time]),
    FILTER(ProcessTable, ProcessTable[Start Time] < CurrentStepTime)
)
RETURN DATEDIFF(PreviousStepTime, CurrentStepTime, MINUTE)

If you want to compute the total production time for a serial number, you can use the following DAX measure:

Total Production Time (Minutes) = 
CALCULATE(
    DATEDIFF(MIN(ProcessTable[Start Time]), MAX(ProcessTable[End Time]), MINUTE),
    ALLSELECTED(ProcessTable)
)

To enhance interactivity, consider enabling Drill-through functionality so users can right-click on a serial number and view detailed production history. Additionally, you can apply Conditional Formatting to highlight delayed steps or bottlenecks based on elapsed time. If you prefer a visual representation of the production timeline, you can use a Gantt Chart for Power BI to show process flow over time.

Power BI is fully capable of handling production data analysis, provided that the data model is structured efficiently, process tables are properly appended or related, and DAX measures are used to compute elapsed times and identify bottlenecks. Let me know if you need a sample Power BI file with this setup.

Best regards,