How to group values

[friend11_6]

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. 

[v-pgoloju]

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

[v-pgoloju]

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

[v-pgoloju]

Hi @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

[v-pgoloju · Accepted solution]

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

[friend11_6]

Hi,

Wondering if there is any solution to this problem? 

[friend11_6]

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?

[Shravan133]

Not necessary. Try it with your existing columns.

[friend11_6]

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

[Shravan133]

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

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

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