Forum Discussion

Larry_Wang's avatar
Larry_Wang
Icon for Helper I rankHelper I
4 years ago
Solved

How to change values with DAX

Hi Everyone,   I have a table below and I want to create a new column called category with only two items, fruit & other. How to do this with DAX?   ID Name CATEGORY 1 APPLE fruit 2 ...
DAX
  • ValtteriN's avatar
    ValtteriN
    4 years ago

    Hi,

    Here is an example:

    Category = IF(Category[Name] in {"TABLE","PEN"},"OTHER","FRUIT")


    I hope this helps to solve your issue and if it does consider accepting this as a solution and giving the post a thumbs up!

Larry_Wang's avatar
Larry_Wang
Icon for Helper I rankHelper I
4 years ago

Hi ValtteriN 

 

May be one more question. I have a similar table below and I also want to create a new column called region. Is there an easy way to do this using DAX?

I tried to duplicate a column in Query editor and use replace values command. But it doesn't support for using of wild cards like " * ". If I have many values then I need to do it one by one. For example how to change all location name with a suffix " Asia " to " APAC " in new column?

 

LocationRegion (New Column)
America/MexicoAMERICA
America/New yorkAMERICA
Asia/CalcuttaAPAC
Asia/ShanghaiAPAC
Asia/TokyoAPAC
Euro/ItalyEU
Europe/LondonEU
Europe/ParisEU
US/EasternAMERICA

 

Thanks,

 

Larry

 

AlexisOlson's avatar
AlexisOlson
Icon for Super User rankSuper User
4 years ago

You probably want a switch for something like this. For example,

Region =
VAR Loc = Table1[Location]
RETURN
    SWITCH (
        TRUE (),
        LEFT ( Loc, 7 ) = "America", "AMERICA",
        LEFT ( Loc, 4 ) = "Asia", "APAC",
        LEFT ( Loc, 4 ) = "Euro", "EU",
        LEFT ( Loc, 3 ) = "US/", AMERICA
    )

 

  • Larry_Wang's avatar
    Larry_Wang
    Icon for Helper I rankHelper I
    4 years ago

    Hi AlexisOlson ,

     

    Cool! It works for me. Thanks a lot for the help.

     

    Larry