New column based on values/string of two other columns

[rsty]

Im new to PBI and trying to recreate an expression from another package and having some issues due to inexperience with PBI.

Im trying to create a new column with string "pass" or "fail".Below is generaly what im trying to do

new =

if(

[col01] = ''abc'' && [col02] > 20, ''fail'', elseif

[col01] = ''def'' && [col02] > 50, ''fail'', elseif

[col01] = ''ghi'' && [col02] > 100, ''fail'', pass

)

Questions:

1. Is there an elseif type function in PBI?

2. Without trying to nest and using the follwing expression i get the following warning.

new = IF( [col01] = ''abc'' && [col02] > 20, ''fail'', "pass")

DAX comparison operations do not support comparing values of type Text with values of type Integer. Consider using the VALUE or FORMAT function to convert one of the values.

is there a solution to this?

Advice,  links to pages that may help greatly appreciated. rsty 

[tamerj1 · Accepted solution]

Hi @rsty 
Yes you can use SWITCH function and no need to type "elseif" as the comma " , " means "Then", "elseif" or "else" depending on its position. Please use

=
SWITCH (
    TRUE (),
    [col01] = "abc"
        && [col02] > 20, "fail",
    [col01] = "def"
        && [col02] > 50, "fail",
    [col01] = "ghi"
        && [col02] > 100, "fail",
    "pass"
)

[rsty]

Totally worked. Thanks

[Jihwan_Kim]

Hi,

Please share your sample pbix file's link, and then I can try to check what is the problem having an error message.

->Please check the data type of the [col02] column. -> May be text data type. -> Change this to Number data type in Power Query Editor.

Please try the calculated column like below.

new column CC =
SWITCH (
    TRUE (),
    [col01] = "abc"
        && [col02] > 20, "fail",
    [col01] = "def"
        && [col02] > 50, "fail",
    [col01] = "ghi"
        && [col02] > 100, "fail",
    "Pass"
)