How to change values with DAX

[Larry_Wang]

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	PEACH	fruit
3	ORANGE	fruit
4	ORANGE	fruit
5	PEACH	fruit
6	PEACH	fruit
7	PEACH	fruit
8	APPLE	fruit
9	TABLE	other
10	PEN	other
11	GRAPE	fruit
12	GRAPE	fruit
13	TABLE	other
14	PEN	other
15	PEN	other
16	APPLE	fruit
17	ORANGE	fruit
18	PEN	other
19	GRAPE	fruit

Thanks,

Larry

[Larry_Wang]

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?

Location	Region (New Column)
America/Mexico	AMERICA
America/New york	AMERICA
Asia/Calcutta	APAC
Asia/Shanghai	APAC
Asia/Tokyo	APAC
Euro/Italy	EU
Europe/London	EU
Europe/Paris	EU
US/Eastern	AMERICA

Thanks,

Larry

[AlexisOlson]

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]

Hi @AlexisOlson ,

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

Larry

[ValtteriN · Accepted solution]

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]

Hi @ValtteriN ,

Great! It works well for me. Thank you for the help😊

Larry