Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

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

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

1 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/

 

Connect with me in LinkedIn: https://pr.linkedin.com/in/adolfosocorro
Follow me on Twitter: https://twitter.com/AdolfoSocorro

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

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
Not applicable

Hi @Anonymous,

 

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

 

switchFunction.png

 

#I'M Not An Expert#

itchyeyeballs
Impactful Individual
Impactful Individual

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

 

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

 

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

 

Connect with me in LinkedIn: https://pr.linkedin.com/in/adolfosocorro
Follow me on Twitter: https://twitter.com/AdolfoSocorro
ashishrj
Power Participant
Power Participant

@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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.