Forum Discussion
Assigning a Production Route Status to AssemblyID
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.
5 Replies
- Ashish_Mathur
Super User
Thanks for your response, Ashish. I tried using the DAX from your sample file in my report, but it appears to be incompatible with data from related tables. In the table visual I am using the Assembly Mark from the related Assemblies table instead of AssemblyID, which is more familiar to the user. I also have slicers for Job # and Sequence from two other related tables (Jobs & Sequences). Using your "Measure" measure as the status column in the table visual seemingly breaks the ability to filter with these slicers, as the full database of Assembly Marks now shows in the table visual.
Additionally, I would prefer to filter the stations by StationType = 0 rather than StationID<>24. This is because Field stations (StationType = 1) may be added in the future, and they should never be included in the Production status.
- Ashish_Mathur
You are welcome. In your original post, you never mentioned that you had multiple tables. How would i be expetced to know that? If you need my help, please share the link from where i can download your PBI file and build the table/slicers that you want. I will write the measure.
