Forum Discussion
Calculated Column return column Header Title if a condition is met - ie cell has a 1 in it.
Hi there
I have a data set from a recent working from home survey that requested respondents to select their top 3 from some 5 options.
The survey data set looks like below and i'm keen to reflect in a calculated column (1, 2 and 3) the Header Title (ie #1 Option, #2 Option, etc)
I considered some sort of nested if formula, but i need for the dax to look across all cells under the option and then return the header title.
Is this possible??
| #1 Option | #2 Option | #3 Option | #4 Option | #5 Option | 1 | 2 | 3 |
| 2 | 3 | 1 | #5 Option | #1 Option | #3 Option | ||
| 1 | 2 | 3 | #2 Option | #4 Option | #5 Option | ||
| 3 | 1 | 2 | #2 Option | #3 Option | #1 Option |
Cheers
Tim
5 Replies
Anonymous can you paste the data and expected output as separate instead side by side, it is hard to understand what is going on there,
thanks... here's attempt 2 🙂
my data set as it currently looks, with 5 options and employee's selection of their Top 3.
Name #1 Option #2 Option #3 Option #4 Option #5 Option Employee 1 2 3 1 Employee 2 1 2 3 Employee 3 3 1 2 I'm keen to create some calculated columns that reflect the Header Title corresponding to the Employee's selection.
Calc Columns>> 1 2 3 Employee 1 #5 Option #1 Option #3 Option Employee 2 #2 Option #4 Option #5 Option Employee 3 #2 Option #3 Option #1 Option chasing a forumla that will return the Header Title corresponding to the number in the cell beneath it.
hope this makes more sense.
Tim
Anonymous , The data is not making it clear.
I think Unpivot or transpose should help you to put data in a better format
https://radacad.com/pivot-and-unpivot-with-power-bi
https://yodalearning.com/tutorials/power-query-helps-transposing-data/
attempt #3
current data set...
Survey Question. From the 5 options, rank your Top 3 challenges whilst working from home? Name #1 Option #2 Option #3 Option #4 Option #5 Option Employee 1 2 3 2 Employee 2 1 2 3 Employee 3 3 1 2 I'd like to have a calculated Column for each Ranking... ie #1 challenge, #2 Challenge and #3 Challenge reflecting the Option selected by the Employee.
Using the data set above, the calculated colums would look like the following:
Employee Name
#1 Challenge #2 Challenge #3 Challenge Employee 1 #5 Option #1 Option #3 Option Employee 2 #2 Option #4 Option #5 Option Employee 3 #2 Option #3 Option #1 Option third times a charm ??
Tim
