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.

redsquigglyinoperator

  • 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 }

warningAlthough 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.

 

Advertisements
DAX “IN” Operator

9 thoughts on “DAX “IN” Operator

    1. Yes, you can use the IN operator in RLS expressions. Since there is no IntelliSense there, you don’t even see the red squiggly. Of course IN is so new that it’s only available in Power BI, not PowerPivot or Analysis Services.

      Liked by 1 person

  1. Mr. Wang regarding your answer to Tony on ‘NOT’. Does this mean the entire expression is deMorgan’ed like is the case with SQL’s NOT IN operator (that would mean that a single BLANK value would make the entire predicate fail)?

    Like

    1. There are two separate operators involved here: the IN operator and the NOT operator. IN operator has precedence over NOT operator, so NOT [Column] IN { Value1, Value2 } is treated as NOT ([Column] IN { Value1, Value2 }).
      Not sure about the BLANK value question. In SQL, NULL means unknown therefore NULL anywhere renders the entire predicate unknown. But in DAX, BLANK compares equal to FALSE. DAX BLANK does not mean unknown as in SQL but casts to FALSE when converted to Boolean.

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s