Forum Discussion
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 TeamIf this post helps then please mark it as a solution, so that other members find it more quickly.
Thank you.
| Client Code | Step Name | Completion Date | Step Group | Step Sort Order |
| Client1 | 1 Return required | Return required | 1 | |
| Client1 | 2 Individual | Return required | 1 | |
| Client1 | 2 Individual letter type | Return required | 1 | |
| Client1 | 2 Partnership | Return required | 1 | |
| Client1 | 2 Partnership letter type | Return required | 1 | |
| Client1 | 2 R40 | Return required | 1 | |
| Client1 | 2 R40 letter type | Return required | 1 | |
| Client1 | 2 R40 to client | Return required | 1 | |
| Client1 | 2 R40 to client OneClick | Return required | 1 | |
| Client1 | 2 Return type | Return required | 1 | |
| Client1 | 2 SA100 to client | Return required | 1 | |
| Client1 | 2 SA100 to client OneClick | Return required | 1 | |
| Client1 | 2 SA100 to H and W | Return required | 1 | |
| Client1 | 2 SA100 to H and W OneClick | Return required | 1 | |
| Client1 | 2 SA100 to Sole Trader | Return required | 1 | |
| Client1 | 2 SA100 to Sole Trader OneClick | Return required | 1 | |
| Client1 | 2 SA800 to Partnership | Return required | 1 | |
| Client1 | 2 SA800 to Partnership OneClick | Return required | 1 | |
| Client1 | 2 SA900 to Trust | Return required | 1 | |
| Client1 | 2 SA900 to Trust OneClick | Return required | 1 | |
| Client1 | 2 Sole Trader | Return required | 1 | |
| Client1 | 2 Sole Trader letter type | Return required | 1 | |
| Client1 | 2 Trust | Return required | 1 | |
| Client1 | 2 Trust letter type | Return required | 1 | |
| Client1 | 3 Information received | Info received | 2 | |
| Client1 | 4 Further client info required | Queries with client | 3 | |
| Client1 | 5 Further info received | Awaiting internal review | 4 | |
| Client1 | 6 Internal info required | Awaiting review | 5 | |
| Client1 | 7 Internal info received | Awaiting review | 5 | |
| Client1 | 8 Sent for review | Awaiting review | 5 | |
| Client1 | 9 Review complete | Waiting to be sent | 6 | |
| Client1 | 10 Sent to client | With client | 7 | |
| Client1 | 11 Submitted to HMRC | Submitted | 8 | |
| Client1 | 12 All work completed | Complete | 9 | |
| Client7 | 1 Return required | 12/05/2025 | Return required | 1 |
| Client7 | 2 Individual | 12/05/2025 | Return required | 1 |
| Client7 | 2 Individual letter type | 12/05/2025 | Return required | 1 |
| Client7 | 2 Partnership | Return required | 1 | |
| Client7 | 2 Partnership letter type | Return required | 1 | |
| Client7 | 2 R40 | Return required | 1 | |
| Client7 | 2 R40 letter type | Return required | 1 | |
| Client7 | 2 R40 to client | Return required | 1 | |
| Client7 | 2 R40 to client OneClick | Return required | 1 | |
| Client7 | 2 Return type | 12/05/2025 | Return required | 1 |
| Client7 | 2 SA100 to client | 12/05/2025 | Return required | 1 |
| Client7 | 2 SA100 to client OneClick | Return required | 1 | |
| Client7 | 2 SA100 to H and W | Return required | 1 | |
| Client7 | 2 SA100 to H and W OneClick | Return required | 1 | |
| Client7 | 2 SA100 to Sole Trader | Return required | 1 | |
| Client7 | 2 SA100 to Sole Trader OneClick | Return required | 1 | |
| Client7 | 2 SA800 to Partnership | Return required | 1 | |
| Client7 | 2 SA800 to Partnership OneClick | Return required | 1 | |
| Client7 | 2 SA900 to Trust | Return required | 1 | |
| Client7 | 2 SA900 to Trust OneClick | Return required | 1 | |
| Client7 | 2 Sole Trader | Return required | 1 | |
| Client7 | 2 Sole Trader letter type | Return required | 1 | |
| Client7 | 2 Trust | Return required | 1 | |
| Client7 | 2 Trust letter type | Return required | 1 | |
| Client7 | 3 Information received | 12/05/2025 | Info received | 2 |
| Client7 | 4 Further client info required | 12/05/2025 | Queries with client | 3 |
| Client7 | 5 Further info received | Awaiting internal review | 4 | |
| Client7 | 6 Internal info required | Awaiting review | 5 | |
| Client7 | 7 Internal info received | Awaiting review | 5 | |
| Client7 | 8 Sent for review | Awaiting review | 5 | |
| Client7 | 9 Review complete | Waiting to be sent | 6 | |
| Client7 | 10 Sent to client | With client | 7 | |
| Client7 | 11 Submitted to HMRC | Submitted | 8 | |
| Client7 | 12 All work completed | Complete | 9 |