topic Re: DAX if statement-evaluate multiple values in one column, return single value in DAX Commands and Tips

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

christielu — Fri, 18 Dec 2020 17:46:08 GMT

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!

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

shep123 — Fri, 18 Dec 2020 18:21:35 GMT

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.

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

christielu — Fri, 18 Dec 2020 20:49:45 GMT

Thank you so much! SWITCH works perfectly.

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

christielu — Fri, 18 Dec 2020 22:43:06 GMT

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!