Forum Discussion

christielu's avatar
christielu
Frequent Visitor
5 years ago
Solved

DAX if statement-evaluate multiple values in one column, return single value

Hi all! I'm wondering if I could write a better IF statement for my problem. I have a "person" column, and I need to create a "location" column based on person's name. There are a lot of names (over ...
  • shep123's avatar
    shep123
    5 years ago

    depends what you mean by endless for which solution is better.

     

    This one has a few nested ifs but not nearly as many:

    Location =
    IF ('Table'[Person_Name] IN { "person1", "person2", "person3" },
    "location1", IF ( 'Table'[Person_Name] IN { "person10", "person11", "person12" },  "location2" ))
     
    This one is my prefered:
    Location_alt =
    SWITCH (
    TRUE (),
    'Table'[Person_Name] IN { "person1", "person2", "person3" }, "location1",
    'Table'[Person_Name] IN { "person10", "person11", "person12" }, "location2"
    )
     
     
    I obviously only did a subset of your data. You probably could do this cleaner doing enter data and making a relationship between the tables on person name but if you want to do a calculated column this is how I would.
     
shep123's avatar
shep123
Helper I
5 years ago

depends what you mean by endless for which solution is better.

 

This one has a few nested ifs but not nearly as many:

Location =
IF ('Table'[Person_Name] IN { "person1", "person2", "person3" },
"location1", IF ( 'Table'[Person_Name] IN { "person10", "person11", "person12" },  "location2" ))
 
This one is my prefered:
Location_alt =
SWITCH (
TRUE (),
'Table'[Person_Name] IN { "person1", "person2", "person3" }, "location1",
'Table'[Person_Name] IN { "person10", "person11", "person12" }, "location2"
)
 
 
I obviously only did a subset of your data. You probably could do this cleaner doing enter data and making a relationship between the tables on person name but if you want to do a calculated column this is how I would.
 
  • christielu's avatar
    christielu
    Frequent Visitor
    5 years ago

    Thank you so much! SWITCH works perfectly.

  • christielu's avatar
    christielu
    Frequent Visitor
    5 years ago

    Hi again! I used SWITCH statement in Excel data model and it worked. But when I used the exact same statement (copy and paste) in SSAS, it gave me an error that the syntax for 'IN' is incorrect. 

    I did some google search and a few people had the same issue but no solution. Do you happen to know why? Thank you!