We have data being provided from software that gives the status on a workflow. It includes status of workflow steps previously completed.
I’m struggling to model the data in SQL and/or create DAX calculations that determine how many orders are in what part of a workflow process.
Need to determine the following
- how many orders have been created and verification process has not started?
- How many orders where the verification process has completed
- How many orders are waiting to be authorized
- How many orders completed authorization process and are ready to schedule
- how many orders have been scheduled
- how many orders has been scheduled and have completed a treatment?
What I’m failing to grasp is how to evaluate multiple rows per order and determine the order’s position in the work flow and then count it in the correct bucket
To explain, the report lists all of the conditions that have completed for each order and multiple conditions can occur on the same date.
If I calculate to include or exclude a Status condition against an order, I’m flummoxed.
For example, exclude all orders that do not have “"ready to schedule", that is true of all orders because all orders have at least one or more rows where status is not “ready to schedule”
See attachedTemp Order Report.xlsx (262.9 KB)