Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to 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.
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.
This worked perfectly! Thanks for your assistance.
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |