I am struggling to work out the logic for assigning an overall route status to production assemblies and display them in a table visual.
All assemblies are assigned a route that consists of specific stations along the production line. The completion of a route depends upon the completion of the full quantity of the assembly at each station within that route. The desired statuses are as follows:
Not Started - Zero quantity completed at any and all stations in route
Complete - Full quantity completed at all stations in route
In Progress - Any quantity > 0 completed at any station in route
I have a database table (StationSummary) that lists the completed station quantity for each station an assembly is routed to. Here's a sample of some of the data from that table:
| AssemblyID | StationID | StationType | PositionInRoute | TotalQty | QtyComplete | PrevStationID | NextStationID |
| 550551 | 6 | 0 | 2 | 1 | 1 | 7 | 24 |
| 550551 | 24 | 1 | 3 | 1 | 0 | 6 | |
| 550559 | 24 | 1 | 3 | 1 | 0 | 6 | |
| 550559 | 6 | 0 | 2 | 1 | 0 | 7 | 24 |
| 550550 | 6 | 0 | 2 | 1 | 0 | 7 | 24 |
| 550559 | 7 | 0 | 1 | 1 | 0 | | 6 |
| 550550 | 24 | 1 | 3 | 1 | 0 | 6 | |
| 550550 | 7 | 0 | 1 | 1 | 1 | | 6 |
| 550551 | 7 | 0 | 1 | 1 | 1 | | 6 |
Ignoring the status of StationID 24, which is not a production station (StationType = 0), the status output should look like this:
| AssemblyID | Status |
| 550550 | In Progress |
| 550551 | Complete |
| 550559 | Not Started |
I'm thinking I would need a DAX column either on the StationSummary table or on the Assembly table, but I don't know how to write it to check the station status of multiple rows and combine it into a single route status for the assembly.
