Forum Discussion

EricKautz's avatar
EricKautz
Helper 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

If Asset Class = "InterestRate" then "InterestRate"

or

If Asset Class = "Commodity" then "Commodity"

or

If Asset Class = "Unspecified" and Trade ID is between 11111111 and 99999999 then "Commodity"

 

Asset ClassTrade IDNew Asset Class

FX

2000000000000FX
InterestRate123456Interest Rate
Commodity

98765432

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

5 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Super 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.

      • ryan_mayu's avatar
        ryan_mayu
        Super 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"))