Forum Discussion
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 30) and lots of locations (10). Instead of writing endless nested IF statement below, is there an easier way to do this?
Just an example of my current statement: if(OR(person_name="person1",person_name="person2"),"location1", IF(OR(person_name="person3" ...
Table1:
Person_Name Location
| person1 | location1 |
| person2 | location1 |
| person3 | location1 |
| person4 | location1 |
| person5 | location1 |
| person6 | location1 |
| person7 | location1 |
| person8 | location1 |
| person9 | location1 |
| person10 | location2 |
| person11 | location2 |
| person12 | location2 |
| person13 | location2 |
| person14 | location2 |
| person15 | location2 |
| person16 | location2 |
| person17 | location2 |
| person18 | location2 |
| person19 | location2 |
| person20 | location3 |
| person21 | location3 |
| person22 | location3 |
| person23 | location3 |
| … | … |
Thank you in advance!
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.
3 Replies
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.Thank you so much! SWITCH works perfectly.
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!
