Calculated Column return column Header Title if a condition is met - ie cell has a 1 in it.

[Anonymous]

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 

[amitchandak]

@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/

[Anonymous]

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 

[Anonymous · Accepted solution]

Hi @Anonymous,

As amitchandak said, do unpivot columns on your '# option' fields to convert them to attribute and value. Then you can create a matrix visual with name on 'row', value on 'column', attribute on value field to achieve your requirement.

Regards,
Xiaoxin Sheng

[parry2k]

@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, 

[Anonymous]

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