Count by step, for every step my work has routed to or through

[JP_Wisco]

I'm trying to write a workflow throughput measure that produces counts for each step based on how many workflows have reached or passed that step. 

• I have a fact table that describes individual workflows, the workflow template being followed, the current step those workflows are on, who the workflow is currently assigned to, and a bunch of other information fields (including half a dozen date fields).
• I also have a dimension table that lists each step in a workflow template and the step's sequential order, for the 48 different templates available. The relationship field for this dimension table and is a simple index column called "wfStepOrder" that was joined to the workflows table based on the workflow template name and the step name.

Here is what I have so far, and I can't think of a way to retain workflow template context while breaking the sequence context within the template. It has me wondering if there is a better way to model the stepOrder table, but I'm struggling to think of a way that doesn't create 48 separate template tables and a boatload of manual model upkeep.

workflow Throughput =

VAR MinStepOrder = MIN(steporder[stepOrder])

RETURN

    CALCULATE(

        [Workflow Count],

        ALL(steporder[wfstepOrder]),

        steporder[stepOrder] >= MinStepOrder

    )

My Alternative SOLUTION: I didn't figure out how to write a DAX throughput measure that would work with the model I had, but I was able to model a true routing history table from my data source rather than just using the current step table. This additional table turns my star schema into more of a snowflake, but I think it will still work for my reporting needs. I addressed skipped steps with a fill down transformation on the routing sign-off column with nulls where the currentStepOrder was >= the routedStepOrder. Prior to the fill down, a conditional column identifying each step as either "signed-off" or "skipped" based on nulls in the sign-off column retained data accuracy while allowing my measures to count each step as having been "routed through" either by skipping or completing.  

[123abc]

It seems like you're trying to calculate the throughput of workflows at each step in the process. To retain the workflow template context while breaking down the sequence context within the template, you might need to consider a different approach.

One way to achieve this is by creating a calculated table or a measure that dynamically groups workflows by both the template and the step they are currently on. Here's an example of how you might do this:

Workflow Throughput =
VAR CurrentStepOrder = MAX(steporder[stepOrder])
RETURN
CALCULATE(
COUNTROWS('fact table'),
FILTER(
ALL('fact table'),
'fact table'[CurrentStep] <= CurrentStepOrder
)
)

In this measure:

• CurrentStepOrder retrieves the maximum step order observed in your data.
• CALCULATE is used to modify the context of the calculation.
• COUNTROWS counts the number of rows (workflows) that meet the conditions.
• FILTER retains only the rows where the current step is less than or equal to the maximum step order observed.

This calculation should give you the count of workflows that have reached or passed each step, retaining the workflow template context.

Ensure that your 'fact table' contains the necessary columns to identify the current step of each workflow and the workflow template being followed.

Adjust the DAX expression according to your table and column names. This is just a template to get you started.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[JP_Wisco]

I can't seem to find a way to make this DAX work even in the broadest context. I think I am going to take your advice to find a different approach and work up a different way to model the data table for this reporting.

[123abc · Accepted solution]

It sounds like you're considering restructuring your data model to better facilitate the analysis of workflow throughput. This can be a good approach if you're finding it difficult to implement the desired calculations using your current data model.

Here are a few suggestions for restructuring your data model:

1. Denormalize Data: Instead of having separate fact and dimension tables, consider denormalizing your data model. You could combine relevant information from the fact and dimension tables into a single table. This can simplify your DAX calculations and improve performance.

2. Aggregate Data: Depending on your reporting requirements, you may be able to pre-aggregate your data to calculate workflow throughput more efficiently. For example, you could create summary tables that aggregate workflow counts by step and workflow template.

3. Consider Different Granularity: If your current data model doesn't provide the necessary granularity for your analysis, consider capturing more detailed information about workflow steps, such as timestamps for each step transition. This additional granularity can provide valuable insights into workflow throughput.

4. Explore Data Modeling Tools: Depending on your data modeling requirements, you may want to explore different data modeling tools or techniques. Tools like Power BI, Tableau, or SQL Server Analysis Services (SSAS) offer powerful capabilities for building and analyzing data models.

By exploring these approaches and considering your specific reporting requirements, you can develop a data model that better supports your workflow throughput analysis. Don't hesitate to experiment with different approaches to find the best solution for your needs.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.