Forum Discussion
How to count different values in the same row
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 |
*******************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.
- Load Data into Power Query:
5 Replies
*******************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.
- 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!
- 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?
- Load Data into Power Query:
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:
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.
Clean the Data:
- Filter out rows where the Employee column is blank.
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.
Load Data to Power BI.
Steps in Power BI:
Create the SurveyCount Measure:
SurveyCount = COUNTROWS(SurveyTable)This counts the number of rows for each employee, representing the surveys they participated in.
Create the SurveyScore Measure:
SurveyScore = SUM(SurveyTable[Score])This sums up the scores for each employee.
Build the Report Table:
- Add a table visual.
- Drag EmployeeName, SurveyCount, and SurveyScore into the table.
Final Result:
EmployeeName SurveyCount SurveyScoreJohn 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.
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
