Forum Discussion
Return column name each time specific cell value is detected?
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!
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.
2 Replies
v-xicaiCommunity Support
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.