Forum Discussion

obothewizard's avatar
obothewizard
Helper I
5 years ago
Solved

Create column based on multiple values

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 ...
Need Help
  • AlB's avatar
    AlB
    5 years ago

    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.

     

ibarrau's avatar
ibarrau
Super User
5 years ago

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's avatar
    obothewizard
    Helper I
    5 years ago

    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 🙂