Forum Discussion
Help with DAX logic - Finding largest value in each row and applying that score and descriptor
I have been tasked with building a report in powerbi, but I am very new and having issues with using logic via DAX.
The project: take in skill assessment surveys about an employee; two data sets, one survey for a self assessment done by the employee, then another survey, same exact questions, but done by the employees manager. So, we have a self assessment and a manager's assessment of that same employee. Responses to both align and match.
There are 65 questions for each survey, and 5 responses to answer. The 5 responses (or proficiencies) are either: Influencing, Enabling, Establishing, Developing, and Emerging. These come with scores of 5, 4, 3, 2, and 1 respectively (Influencing being great, and Emerging being a new skill). Each question relates to a skill, and there are multiple questions that align to each skill. There are 20+ skills being measured.
I need to "pivot" the data so that it is refined to just the skill level with sums of the scores by each of those responses. I have gotten this far, but this is where I have hit a wall. At this point, I need to take the data as pasted below, and I need help with the logic that gives me the name of the proficiency with the highest scores at the rolled up skill level (not by each of the 65 questions).
Here is how I built this table, which works to the extent of summing scores by skill (again multiple questions roll up to each skill):
Below is my logic for finding the max score ("Sum of MaxResponse_SA_Score) - of which I am not even sure I need:
Below is my logic for finding the max proficiency ("First of MaxResponse_SA_Score):
Once I get this figured out, I have to do the same against the Leader Assessed survey. I then have to compare proficiencies from one against the other to see how the two align (the self assessment vs the leader assessed) by skill. But, I need to get beyond this obstacle first. Any advice is greatly appreciated.
Thanks,
JMR
3 Replies
- lbendlin
Super User
One of the first things you will learn is to bring your data into a usable format by unpivoting those columns in the data source ingestion in Power Query (no worries, you can pivot them back in the matrix visual).
Once that is done your formulas will become trivial. No longer will you need to use those nested IF statements or nested MAX. And the solution will work for any number of "columns" (questions/answers) with no need to hard code anything.
- AnonymousNot applicable
Hi JohnnyR ,
It's been a while since I heard back from you and I wanted to follow up. Have you had a chance to try the solutions that have been offered? If the issue has been resolved, can you mark the post as resolved? If you're still experiencing challenges, please feel free to let us know and we'll be happy to continue to help!
Looking forward to your reply! - JohnnyRFrequent Visitor
Resolved, but in a different way than anything that was proposed. Thanks for your reply and willingness to help.