Forum Discussion

hayleypnch's avatar
hayleypnch
New Member
4 years ago

Custom Column with isblank and isnotblank

Hi All, 

 

I'm looking at creating a custom column based on the contents of 2 other columns. I want to say:

 

If column 1 and column 2 are both blank, display "outcome 1" in the column

 

If column 1 is not blank and column 2 is blank, display "Outcome 2" in the column

 

If Column 2 is not blank, display "Outcome 3" in the column.

 

I have written this:

 

if(ISBLANK [Column1] and ISBLANK[Colmun2], "Outcome1",
if(ISNOTBLANK [Column1] and ISBLANK [Column2],"Outcome2",
if(ISNOTBLANK[Column2], "Outcome3" ))))

 

But I'm getting an error under the "Outcome1" section. Any ideas?

 

Need Help

2 Replies

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    4 years ago

    Here is a column expression that should work.

     
    Outcome = SWITCH(TRUE(),
    Blanks[Column1] = "" && Blanks[Column2]="", "Outcome 1",
    Blanks[Column2]="", "Outcome 2",
    NOT(Blanks[Column2]=""), "Outcome 3")
     
    Pat
     
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi hayleypnch ,

     

    •  Add a Custom column In Power Query
    =if Text.Length([Column1])=0 and Text.Length([Column2])=0 then "Outcome1" else if Text.Length([Column1])>0 and Text.Length([Column2])=0 then "Outcome2" else "Outcome3"

     

    • Use DAX to add a column:
    New Column = 
SWITCH (
    TRUE (),
    [Column1] = BLANK ()
        && [Column2] = BLANK (), "Outcome1",
    [Column1] <> BLANK ()
        && [Column2] = BLANK (), "Outcome2",
    [Column2] <> BLANK (), "Outcome3"
)

     

    Best Regards,
    Eyelyn Qin
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.