Create column based on multiple values

[obothewizard]

Hi there,

I am trying to create a calculated column based on a Zipcode table. I would like to add a field to indicate the US state based on the existing area field in the data table. For example I want to categorise New York where values in Area field are Bronx, Brooklyn, Queens etc.

Simply put it would be something like this in SQL :

State = IF Area IN ("Bronx", "Brooklyn", "Queens" ....) THEN "New York"
            ELSE IF Area IN ("Bergen County", "Essex County"...) THEN "New Jersey"

How can I replicate this logic for DAX? I have seen lots of articles on multiple conditions, but nothing so far describing how to categorise multiple values under one new column value. My use of SWITCH and IF logic is not allowing me to do this but maybe I'm implementing incorrectly.... 

Hope this makes sense, any help/pointers greatly appreciated 🙂

Screenshot of data table for reference!

Thanks!

[AlB · Accepted solution]

Hi @obothewizard 

Create a calculated column:

State =
SWITCH (
    TRUE (),
    Table1[Area] IN { "Bronx", "Brooklyn", "Queens" }, "New York",
    Table1[Area] IN { "Bergen County", "Essex County" }, "New Jersey"
)

 where you'll have to complete  { "Bronx", "Brooklyn", "Queens" }  and { "Bergen County", "Essex County" } with all the values 

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[obothewizard]

Hey thanks so much! I had written exactly the same logic when trying to write the DAX but I was using "(" instead of { !!

Thanks for the response 🙂

[ibarrau]

Hi, you can do it in a "New Column" in Power query (transform data) or DAX. Let's see DAX because you asked for it 🙂

NewColumn = 
IF ( 
    Table[Area] IN {"Bronx", "Brooklyn", "Queens" ....} 
    , "New York"
    , IF (
        Table[Area] IN {"Bergen County", "Essex County"...}
        , "New Jersey"
        , "Other"
    )
)

Hope that helps,

[obothewizard]

Hi there,

Thanks so much for your response. I actually used the calculated field with a switch statement as marked above, but thanks for this alternative approach - it can't hurt to have other ways of doing stuff!

Cheers 🙂