Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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.
Solved! Go to 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()
)
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
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:
Please help
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
86 | |
84 | |
66 | |
62 | |
59 |
User | Count |
---|---|
197 | |
116 | |
107 | |
77 | |
70 |