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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Solution Sage
Solution Sage

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
Solution Sage
Solution Sage

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
Solution Sage
Solution Sage

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
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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