Forum Discussion
How to group values
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
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.