Power BI – DAX IN Statement

By: David Rohlfs

Introduction

I think for many of us updates in the Microsoft world can just fly by without you even realizing. Features are added, programs are deprecated, and the user can be entirely oblivious until the changes happen. Well, we aren’t tackling that problem today, but one of those features added that you may have missed was the addition of IN in DAX!

In this blog, we’ll focus on how to use the IN function in DAX. Since our goal is to explore this specific DAX functionality, we won’t cover the entire process of building a measure here. If you find yourself needing guidance on creating DAX measures, I recommend checking out some of the related blogs listed below for additional support and resources.

When to use it

IN is a function inside of DAX that essentially creates a nested OR clause. This is very similar to other languages (including SQL) where you say [Value] IN {[option 1], [option 2], [etc.]} and mean if OR([Value] = [option 1], OR ([Value] = [option 2], [Value] = [option 3])). Essentially, we get a new way to write OR statements!

The basic use case for IN is similar to a normal use of OR in DAX except that now we are looking for many options. Instead of saying

Color Rainbow 1 =
VAR _Sum = SUM(‘Sales’[Total])
VAR _Colors = OR(OR(‘Sales’[Color] = "Red", ‘Sales’[Color] = "Orange"), OR(OR(‘Sales’[Color] = "Yellow", ‘Sales’[Color] = "Green"), OR(‘Sales’[Color] = "Blue", OR(‘Sales’[Color] = "Indigo", ‘Sales’[Color] = "Violet"))))
RETURN
CALCULATE(_Sum, _Color)

You can say

Color Rainbow 2 =
VAR _Sum = SUM(‘Sales’[Total])
VAR _Colors = {"Red", "Orange", "Yellow", "Green", "Blue", "Indigo", "Violet"}
RETURN
CALCULATE(_Sum, ’Sales’[Color] IN _Colors)

Notice how much simpler the _Colors statement is! Instead of needing to correctly nest all of your OR statements and set up the conditions, you can just list the options.
I know this isn’t a huge improvement for some of you, but if you’ve tried creating an OR statement that has more than 2 options, you know how quickly the equation can blow up on you! While this is still early in DAX, it should immediately be regarded as best practice. It isn’t just the cleanliness of the DAX equation, but it is also about the performance.

When you get into many options that you are evaluating, simplifying your DAX with IN instead of nested OR will help your performance. From my tests, it seems like OR is a little faster with less options (only 2 options in OR), but when you get into nesting the OR functions, they are worse than using an IN. To put it simply, it is easier to look at one function (a single IN statement) vs. multiple functions (nested OR statements).

Conclusion

This is one of those things that gets updated in Microsoft that is just necessary. DAX IN will greatly simplify OR syntax in DAX and also help performance a little. If you enjoyed this blog, I recommend reading through some of the related blogs below. Many of them are similar to OR or IN functions and are often used alongside each other.

Links Related to This Blog:

Power BI – DAX Switch Conditional Statement

Power BI Switch Function in DAX

Power BI – DAX Calculate Function

Power BI – Understanding the Basics of Using Variables in DAX

Power BI – DAX IF Statements

Power BI – DAX Selected Value

Comments