Isolating most recent for a matrix visual

[nikki11]

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

[v-mdharahman · Accepted solution]

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.

[v-mdharahman]

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.

[nikki11]

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

[v-mdharahman]

Hi @nikki11,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution so that other community members can find it easily.

Thank you.

[v-mdharahman]

Hi @nikki11,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[nikki11]

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

[nikki11]

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

[nikki11]

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