How to count different values in the same row

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.