How to do an "IN" type command in PowerBI language

[Anonymous]

I'm new to PowerBI and trying to figure out what I think is a simple formula.

I want to return  TRUE if a column value is in a list of values.

i.e.

IF table.field IN ("A","D","G") THEN "Yes" ELSE "No"

As you can see from my example, I don't think in PowerBI language,

How do you do a simple statement like thie in PowerBI?

Thanks

[Anonymous]

Thanks for the help. This told me what I needed.

The SQLBI website was helpful for me to transition from a SQL syntax to a DAX syntax. 

The SWITCH function seems like the most likely tool I'll use.

If DAX doesn't have an IN function, which seems so basic to me, are there many other surprises I should expect as I learn DAX?

[Anonymous]

Hi @Anonymous,

   I agree with @ashishrj. I think that your best option is to use the SWITCH function.

#I'M Not An Expert#

[itchyeyeballs]

In the absence of an actual IN function, an alternative solution would be to set up a dimension table, its more work at the outset but potentially easier to work with and gives more flexibility

You would just need the dimension table to look something like:

table.field | Lookup

A | Yes

B | No

C | No

D | Yes

then link to your fact table.

If you don't want to list all the possible values then you could just include the matching ones and set up a measure to turn null

into "No"

using this approach you can also have other levels of aggregation to suit different needs

table.field | Lookup | Lookup2

A | Yes | Include

B | No | Include

C | No | Exclude

D | Yes | Exclude

[asocorro · Accepted solution]

Check out this article, From SQL to DAX: IN and EXISTS:

http://www.sqlbi.com/articles/from-sql-to-dax-in-and-exists/

[ashishrj]

@Anonymous You can try out using multiple if statement...Eg if(value="A","Yes,if(value="D","Yes",...),"No")

or you can also use switch