Forum Discussion

EricKautz's avatar
EricKautz
Icon for Helper I rankHelper I
2 years ago
Solved

create column with multiple if/or

I have a column that has different asset class names (FX, InterestRate, Commodity, and Unspecified). I want to create a new column with the below if statement.   If Asset Class = "FX" then "FX" or...
Need Help
  • ryan_mayu's avatar
    ryan_mayu
    2 years ago

    EricKautz 

    if you want to use DAX, pls try this

     

    Column = if('Table'[Asset Class] in {"FX","InterestRate","Commodity"},'Table'[Asset Class], if('Table'[Asset Class]="Unspecified" && 'Table'[Trade ID]>="11111111" && 'Table'[Trade ID]<="999999999","Commodity"))
     
     
ryan_mayu's avatar
ryan_mayu
Icon for Super User rankSuper User
2 years ago

EricKautz 

you can try this in Power query

 

if [Asset Class]="FX" then "FX" else if [Asset Class]="InterestRate" then "InterestRate" else if [Asset Class] = "Commodity" then "Commodity" else if [Asset Class]="Unspecified" and [Trade ID]>="11111111" and [Trade ID]<"99999999" then "Commodity" else null

 

 

 

i used double quotation marks for number because I didn't change it to number. If I did that, the first number in the Trade ID will display in different type. If the Trade ID column is in number type in your real data. Then you just remove the double quotation marks in the M code.

  • EricKautz's avatar
    EricKautz
    Icon for Helper I rankHelper I
    2 years ago

    Could you post the actual dax formula you used.

    • ryan_mayu's avatar
      ryan_mayu
      Icon for Super User rankSuper User
      2 years ago

      EricKautz 

      if you want to use DAX, pls try this

       

      Column = if('Table'[Asset Class] in {"FX","InterestRate","Commodity"},'Table'[Asset Class], if('Table'[Asset Class]="Unspecified" && 'Table'[Trade ID]>="11111111" && 'Table'[Trade ID]<="999999999","Commodity"))
       
       
      • EricKautz's avatar
        EricKautz
        Icon for Helper I rankHelper I
        2 years ago

        This worked as intended, thank you.