DAX “IN” Operator

It’s hard to keep a secret nowadays. Although Microsoft has not officially announced it, people have discovered on their own that DAX has a new IN operator. Let me explain why the Power BI team have not made this public yet.

• The IntelliSense work has not completed yet so although a DAX expression containing the new syntax will be accepted by the engine, it will be red squiggly underlined in the DAX editor.

• Users cannot use the new syntax directly as a setfilter argument to the CALCULATE family of functions. So the following syntactic shortcut is not yet accepted by the DAX engine.

CALCULATE([Total Sales], 'Product'[Color] IN { "Red", "Green" }) // Not working right now.

You have to use the full version instead:

CALCULATE(
    [Total Sales],
    FILTER(ALL('Product'[Color]), [Color] IN { "Red", "Green" })
)

If you are unfazed by these limitations which should be addressed early next year, feel free to enjoy the new convenience in DAX today.

'Product'[Color] IN { "Red", "Green" }

or

('Product'[Color], 'Customer'[Country]) IN { ("Red", "Russia"), ("Italy", "Green") }

By the way, since DAX is a functional language, there is an equivalent new function CONTAINSROW:

CONTAINSROW({ "Red", "Blue" }, 'Product'[Color])

or

CONTAINSROW(
    { ("Red", "Russia"), ("Italy", "Green") },
    'Product'[Color],
    'Customer'[Country]
)

The examples above show off not just the IN operator, but also a new way to construct a table expression from a list of values or tuples. As with other DAX functions and operators, the new syntax is fully composable as illustrated in the following examples of valid DAX expressions:

'Product'[Color] IN VALUES('Product2'[Color])

{ VALUES('Date'[Year]) - 1, VALUES('Date'[Year]), VALUES('Date'[Year]) + 1 }

Although the following syntax is also accepted by the DAX engine today, it is not by design so you should only use curly braces {} instead of parentheses () to enclose a list of values or tuples, the latter of which should be enclosed in ().

'Product'[Color] IN ( "Red", "Green" ) // Do not do this. Replace () by {} instead.