Forum Discussion
How to group values
Hi,
I have the data in the following table
| Employe ID | Employee Name | Employee | per Record1 | per Record 2 | Record 2 - Record 1 |
| 123 | John Max | 123 John Max | 1 | -1 | |
| 123 | Max John | 123 Max John | 1 | 1 | |
| 456 | Jane Robertson | 456 Jane Robertson | 1 | 1 | |
| 789 | Peter Murray | 789 Peter Murray | 1 | -1 | |
| 167 | Harrison Ford | 167 Harrison Ford | 1 | -1 |
and need help to produce a matrix report in the following format:
| Record 2 - Record 1 | |
| +Reconciled | 0 |
| ++123 | 0 |
| +++123 John Max | -1 |
| +++123 Max John | 1 |
| +Not in Record 1 | 1 |
| ++456 | 1 |
| ++ 456 Jane Robertson | 1 |
| +Not in Record 2 | -2 |
| ++789 | -1 |
| +++789 Peter Murray | -1 |
| ++167 | -1 |
| +++167 Harrison Ford | -1 |
How do I go about doing it in Power BI? Thanks in advance.
Hi friend11_6,
Thank you for reaching out to the Microsoft Fabric Forum Community.
To group all variations of the same employee under a single classification, you should group by Employee ID only, not by the full Employee string.
Try creating the table with the following DAX.
SummaryTable =
VAR BaseTable = 'EmployeeData'
RETURN
ADDCOLUMNS (BaseTable,
"Group Label",SWITCH (TRUE(),
BaseTable[Record 2 - Record 1] = 0, "Reconciled",
ISBLANK(BaseTable[per Record1]), "Not in Record 1",
ISBLANK(BaseTable[per Record2]), "Not in Record 2",
"Other"))
Thanks & Regards,
Prasanna Kumar
9 Replies
Create a New Column for Classification
We’ll classify each row based on the Record 2 - Record 1 value.
Go to Modeling > New column, and enter:
Classification =
SWITCH(
TRUE(),
'EmployeeData'[Record 2 - Record 1] = 0, "Reconciled",
'EmployeeData'[per Record1] = 0 || ISBLANK('EmployeeData'[per Record1]), "Not in Record 1",
'EmployeeData'[per Record2] = 0 || ISBLANK('EmployeeData'[per Record2]), "Not in Record 2",
"Other"
)
This creates a grouping like:
- Reconciled → Difference = 0
- Not in Record 1 → Missing in Record 1
- Not in Record 2 → Missing in Record 2
- Create a Matrix Visual
Now, add a Matrix visual to your report.
- Rows:
- Classification
- Employee ID
- Employee
- Values:
- Record 2 - Record 1 (use as sum)
- Format the Matrix for Indentation
Power BI will auto-indent based on the hierarchy:
- Classification
- Employee ID
- Employee
This will visually match your required format.
Hi,
Must the values be in measures before the proposed solution could work? I had previously tried the following:
Classification =
SWITCH(TRUE(),
'Record 2 - Record 1 < 0, "Not found in Record 2",
'Record 2 - Record 1 = 0, "Reconciled",
"Not found in Record 1")
With Record 2 - Record 1 created as a Measure.
However, in doing so, there was no record displayed under "Reconciled"
I am not sure if I'd written my Measure incorrectly?Not necessary. Try it with your existing columns.
Hi,
I get the same outcome by applying your proposed solution. I wonder if this is because the Employee cannot be grouped under the same classification given that the order of the name could be different? I don't know how to overcome this problem ...
Hi,
Wondering if there is any solution to this problem?Hi friend11_6,
Thank you for reaching out to the Microsoft Fabric Forum Community.
To group all variations of the same employee under a single classification, you should group by Employee ID only, not by the full Employee string.
Try creating the table with the following DAX.
SummaryTable =
VAR BaseTable = 'EmployeeData'
RETURN
ADDCOLUMNS (BaseTable,
"Group Label",SWITCH (TRUE(),
BaseTable[Record 2 - Record 1] = 0, "Reconciled",
ISBLANK(BaseTable[per Record1]), "Not in Record 1",
ISBLANK(BaseTable[per Record2]), "Not in Record 2",
"Other"))
Thanks & Regards,
Prasanna KumarHi friend11_6,
Just following up to see if the solution provided was helpful in resolving your issue. Please feel free to let us know if you need any further assistance.
Best regards,
Prasanna Kumar
Hi friend11_6,
We wanted to kindly check in to see if everything is working as expected after trying the suggested DAX solution. If there’s anything else we can assist with, please don’t hesitate to ask. Hpayy to help you.
Warm regards,
Prasanna Kumar
Hi friend11_6,
Just a gentle reminder has your issue been resolved? If so, we’d be grateful if you could mark the solution that worked as Accepted Solution, or feel free to share your own if you found a different fix.
This not only closes the loop on your query but also helps others in the community solve similar issues faster.
Best,
Prasanna Kumar
