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 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!

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.

     

4 Replies

  • AlB's avatar
    AlB
    Community Champion
    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.

     

    • obothewizard's avatar
      obothewizard
      Helper I
      5 years ago

      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'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 🙂