Forum Discussion

rsty's avatar
rsty
New Member
4 years ago
Solved

New column based on values/string of two other columns

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 

else
if
IF AND with multiple conditions
  • tamerj1's avatar
    tamerj1
    4 years ago

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

3 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    4 years ago

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

     

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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's avatar
      rsty
      New Member
      4 years ago

      Totally worked. Thanks