Isolating most recent for a matrix visual

Hi nikki11,

Thanks for reaching out to the Microsoft fabric community forum.

It looks like you are trying to build a matrix visualization in Power BI that accurately reflects the current step each client is on within a workflow process. Each client progresses through a standardized 32-step process, and these steps are grouped into 9 broader "Step Groups", ordered by a separate sort order. What you're aiming for is possible in Power BI, but it does require a few specific steps to calculate the latest completed step per client and then count them accordingly. Here's how you can approach this:

* First create a new calculated table (using DAX) that summarizes the current step per client. This table will identify the highest completed step (by Sort Order) for each client:

ClientCurrentStep =
ADDCOLUMNS (
VALUES ( Clients[ClientCode] ),
"CurrentStepGroup",
VAR CompletedSteps =
FILTER (
Workflow,
Workflow[Client Code] = Clients[ClientCode] &&
NOT ISBLANK ( Workflow[Completion Date] )
)
VAR MaxSort =
MAXX ( CompletedSteps, Workflow[Step Sort Order] )
RETURN
IF (
ISBLANK ( MaxSort ),
1, -- default to Step Group 1 if no steps are complete
MaxSort
)
)

* Now join this table back to your Step Group Order table using the Sort Order, and to Clients using ClientCode. That will give you access to the ClientOffice and Step Group Name for the latest completed step.

* Use this new table in your matrix visual, with:

Rows: ClientOffice

Columns: Step Group Name

Values: Count of ClientCode

This way, each client is counted exactly once at their current position in the workflow.

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.
Community Support Team

If this post helps then please mark it as a solution, so that other members find it more quickly.

Thank you.