How to count different values in the same row

[TuesdayMarket]

Hello, I'm not sure how to count separate values in the same row. 

I have a survey that scores an employee's service. The problem is a survey can contain multiple employees and scores. I would like to count how many times an employee has a survey and then get their corresponding score for a report. Thank you.

Employees Table:

EmployeeID	EmployeeName
001	John
002	Matt
003	Jessica
004	Ronald
005	Valerie

Survey Table:

Survey#	Employee1	Score1	Employee2	Score2	Employee3	Score3
001	John	5
002	John	5
003	Matt	5	Jessica	5	Ronald	5
004	Ronald	5	Matt	5
005	John	5	Jessica	5

The result should be:

EmployeeName	SurveyCount	SurveyScore
John	3	15
Matt	2	10
Jessica	1	5
Ronald	2	10
Valerie

[FreemanZ · Accepted solution]

hi @TuesdayMarket 

*******************As suggested by some AI tools***************************

To achieve the desired result using Power Query or DAX, you'll need to transform your Survey Table to a more normalized format, where each survey entry for an employee is on a separate row. This will make it easier to count surveys and sum scores per employee. Here's how you can do it:

• Load Data into Power Query:
  • Load both your Employees and Survey tables into Power Query.
• Transform Survey Table:
  • Select the Survey Table.
  • Use the "Unpivot Columns" feature to convert the Employee and Score columns into rows. You'll need to unpivot twice: once for the Employee columns and once for the Score columns.
  • After the first unpivot, you'll have columns like Attribute (Employee1, Employee2, etc.), Value (John, Matt, etc.), Survey#, Score1, Score2, etc.
  • Filter out the rows where Attribute ends with a number (i.e., Score1, Score2, etc.), leaving only the Employee-related rows.
  • Rename the Value column to EmployeeName.
  • Remove the Attribute column as it's no longer needed.
  • Perform the second unpivot on the Score columns. This time, you'll end up with columns like Attribute (Score1, Score2, etc.), Value (scores), Survey#, and EmployeeName.
  • Filter out rows where Attribute does not end with the number that corresponds to your EmployeeName's unpivoted position (e.g., if EmployeeName came from Employee1, keep only rows where Attribute is Score1).
  • Rename the Value column to SurveyScore.
  • Remove the Attribute column.
• Merge Tables:
  • Merge the transformed Survey table with the Employees table on EmployeeName to ensure you have all employee details.
• Load to Model:
  • Load the transformed data into your Power BI model.

[TuesdayMarket]

• • Filter out rows where Attribute does not end with the number that corresponds to your EmployeeName's unpivoted position (e.g., if EmployeeName came from Employee1, keep only rows where Attribute is Score1).

---

Sorry for the late reply. How would I filter Employee1 and only keep Score1, Employee2 and only keep Score2, and Employee3 and only keep Score3?

[TuesdayMarket]

---

• • Use the "Unpivot Columns" feature to convert the Employee and Score columns into rows. You'll need to unpivot twice: once for the Employee columns and once for the Score columns.

---

Thank you, that did it!

[123abc]

To solve this, you can unpivot the Survey Table in Power Query to make it more manageable for analysis and then use measures or calculated columns in Power BI to compute the desired results.

Steps in Power Query:

1. Unpivot the Survey Table:

   • Load the Survey Table into Power Query.
   • Select the Survey# column and keep it fixed.
   • Select the Employee1, Score1, Employee2, Score2, Employee3, and Score3 columns.
   • Right-click and choose Unpivot Columns.
   • Rename the resulting columns as:
     • Employee (contains names of employees).
     • Score (contains scores).
   • You’ll now have rows for each employee and their corresponding score per survey.

2. Clean the Data:

   • Filter out rows where the Employee column is blank.

3. Merge with the Employee Table:

   • Merge this unpivoted table with the Employees Table on the EmployeeName column if needed. This step ensures all employees (even those with no surveys) are accounted for.

4. Load Data to Power BI.

---

Steps in Power BI:

1. Create the SurveyCount Measure:

   SurveyCount = COUNTROWS(SurveyTable)

   This counts the number of rows for each employee, representing the surveys they participated in.

2. Create the SurveyScore Measure:

   SurveyScore = SUM(SurveyTable[Score])

   This sums up the scores for each employee.

3. Build the Report Table:

   • Add a table visual.
   • Drag EmployeeName, SurveyCount, and SurveyScore into the table.

---

Final Result:

EmployeeName SurveyCount SurveyScore

John	3	15
Matt	2	10
Jessica	1	5
Ronald	2	10
Valerie	0	0

This approach ensures you capture the survey count and corresponding scores correctly for each employee.

[TuesdayMarket]

Thank you for your reply. I have a bit of an inssue on this step:

• Right-click and choose Unpivot Columns.

When I unpivot the table it looks like this:

Survey#	Attribute	Value
001	Employee1	John
001	Score1	5
002	Employee1	John
002	Score1	5
003	Employee1	Matt
003	Score1	5
003	Employee2	Jessica
003	Score2	5
003	Employee3	Ronald
003	Score3	5
004	Employee1	Ronald
004	Score1	5
004	Employee2	Matt
004	Score2	5
005	Employee1	John
005	Score1	5
005	Employee2	Jessica
005	Score2	5

Not sure how to continue from here