Forum Discussion
Isolating most recent for a matrix visual
I have inserted a sample of the data/tables as "replies" to the message to help illustrate at the bottom..
I am struggling in PowerBI/PowerQuery to extract the required information from a table into a visual.
My desired outcome is to present a matrix whereby the Row Value is Client Office, the Column Value is Step Completed (sorted in order) and the values are a total count of clients that are at that current step.
The data I have is as follows:
Table 1 – Clients
This is my primary Client information table which for this exercise contains [Client Code], [Client Name] and [Client Office} + many more.
Table 2 – Workflow
This table lists all active clients using a specific workflow that I need to track and all the steps and whether or not they are complete. Each client will go through 32 steps, so I would expect to see 32 lines per client (approx. 2000 clients). If the step is complete then there will be a completion date available otherwise it will be null. I have added a conditional column that effectively renames the step so that I can summarise the 32 steps in 9, this is called "Step Group Name" the order of which is determined in Table 3.
Table 3 – Step Group Order
This table lists [Step Group Name] and [Sort Order] 1 through to 9.
Relationships are set up as follows:
Table 1 to Table 2 is One to Many via [Client Code]
Table 2 to Table 3 is Many to One via [Step Group Name]
The desired outcome is to evaluate each of the 32 lines per Client and ask the question, what step is the client currently at? If a client has no completion date against any of the 32 steps in the workflow, then the assumption should be they are still at the first step, e.g. Sort Order 1. If there is a date recorded against any of the 32 steps, then it looks at the latest step completed and double checks agains the Sort Order, returning the latest “Step Group Name” from that has been completed.
Therefore the matrix will end up showing a total of clients that are sitting at a particular step in the workflow NOT counting the total of how many clients have achieved that step. Basically, the total value of the matrix should add upto the total amount of clients (e.g. approx 2000) and NOT 2000 x 32.
I have gone round the houses and wonder if anyone can help.
Thank you
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.
8 Replies
SAMPLE DATA/TABLES :
Table 1 Example
ClientCode ClientName ClientOffice Client1 Client Name 1 Office 1 Client2 Client Name 2 Office 1 Client3 Client Name 3 Office 2 Client4 Client Name 4 Office 3 Client5 Client Name 5 Office 2 Client6 Client Name 6 Office 2 Client7 Client Name 7 Office 3 Client8 Client Name 8 Office 1 Client9 Client Name 9 Office 3 Client10 Client Name 10 Office 3 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 Sort Order Step Group Name 1 Return Required 2 Info received 3 Queries with client 4 Awaiting internal review 5 Awaiting review 6 Waiting to be sent 7 With client 8 Submitted 9 Complete 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.
Hi nikki11,
As we haven’t heard back from you, so just following up to our previous message. I'd like to confirm if you've successfully resolved this issue or if you need further help.
If yes, you are welcome to share your workaround and mark it as a solution so that other users can benefit as well. If you find a reply particularly helpful to you, you can also mark it as a solution.
If you still have any questions or need more support, please feel free to let us know. We are more than happy to continue to help you.
Thank you for your patience and look forward to hearing from you.Hi
Thank you so much for your responses. My apologies for the delay in responding, I was on annual leave and have just returned today. I will check out the responses and respond as soon as I can. Thanking you in advance for your assistance.
Nikki
