Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
PowerUser39
New Member

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

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

 

A1A2Custom Column
NN 
YNA1
NYA2
YYA1,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","")
PowerUser39_0-1713980621784.png
 
How to achieve this either by Measure or by Calculated column?

 

1 ACCEPTED SOLUTION
Kaviraj11
Responsive Resident
Responsive Resident

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()

)

View solution in original post

4 REPLIES 4
Kaviraj11
Responsive Resident
Responsive Resident

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

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
Responsive Resident
Responsive Resident

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()

)

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:

PowerUser39_0-1713986363145.png

Please help

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.