Power BI Custom column with multiple IF then conditions from different columns.

[PowerUser39]

Hello,

I am trying to create a custom column from 2 other columns to list column names if the values match "Y". So I have 2 columns namely A1 and A2 and if any of the rows have Y as value then I want that column name to be in this custom column and if both A1 and A2 have "Y" on the same row then I want A1,A2 in that row and of both have "N" then it should be empty as shown below

A1	A2	Custom Column
N	N
Y	N	A1
N	Y	A2
Y	Y	A1,A2

I tried using below IF conditions but it gave me an error"Cannot convert value 'A3' of type Text to type True/False.

IF(MAINT_ACTIVITY_TABLE[A1]="Y","A1","")&&IF(MAINT_ACTIVITY_TABLE[A3]="Y","A3","")

How to achieve this either by Measure or by Calculated column?

[Kaviraj11]

DAX doesn’t support iterating over columns in a row context directly. I recommend unpivoting the table and the create a calculate column.

[PowerUser39]

Ok I will keep digging and in the meantime your initial solution worked for my original post so accepting that as  the solution for the sample data

[Kaviraj11 · Accepted solution]

CalculatedColumn,

=SWITCH(TRUE(),

MAINT_ACTIVITY_TABLE[A1]="N" && MAINT_ACTIVITY_TABLE[A1]="N", BLANK(),

MAINT_ACTIVITY_TABLE[A1]="Y" && MAINT_ACTIVITY_TABLE[A1]="N","A1",

MAINT_ACTIVITY_TABLE[A1]="N" && MAINT_ACTIVITY_TABLE[A1]="Y","A2",

MAINT_ACTIVITY_TABLE[A1]="Y" && MAINT_ACTIVITY_TABLE[A1]="Y","A1,A2",

BLANK()

)

[PowerUser39]

Thanks it worked on the sample table I have with 2 columns and I got the result but in realtime my report has 12 different columns and the custom column I need should lookup all the 12 columns and get the data. So do I need to work all the combinations for 12 columns to get the expected output or is there another simplified way to achieve this?

The real report columns below:

Please help