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.
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.
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
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.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.
