Return column name each time specific cell value is detected?

[Anonymous]

I have a dataset where the rows refer to different people and each colum referers to a different attribute. Each cell has a value of 0 or 1, with 1 indicating that the person has the attribute, and 0 denoting that they do not. 

I need help creating a measure that will look for the 1s in each row and return the column name for each 1 it finds. The expected output based on the data above would be something like:

Person 1: Attribute 2

Person 2: Attribute 5

Person 3: Attribute 1, Attribute 2, Attribute 4

Person 4: Attribute 2, Attribute 3

Person 5: Attribute 1, Attribute 4, Attribute 5

Thanks in advance for your help! 

[v-xicai · Accepted solution]

Hi @Anonymous ,

you can select the three columns Attribute 1- Attribute 5 in Query Editor, then unpivot these column to row data like picture below, click "Close & Apply".

Then create new column1.

Column1= IF(Table1[Value]=1, Table1[Attribute],BLANK())

Finally, you can create calculated table using DAX below.

NewTable = ADDCOLUMNS(SUMMARIZE(Table1,Table1[Person]),"Expected", CALCULATE(CONCATENATEX(Table1, Table1[Column1], " ")))

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

This worked perfectly! Thanks for your assistance.