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:

    [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" }


('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])


    { ("Red", "Russia"), ("Italy", "Green") },

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.


DAX “IN” Operator

13 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)?


    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.


  2. sam says:

    If I need to replicate this in Excel 2013 what would be the equivalent expression


    where [mUserNm] is MID([mUser],FIND(“\”,[mUser])+1,LEN([mUser]))
    and [mUser] is USERPRINCIPALNAME()
    D is a fact table containing Column DEPT
    LOGNM is a user name table with Columns User ID and DEPT


    1. In Excel 2013, you use the following DAX formula. USERPRINCIPALNAME is a new function, I don’t think it exists in older version of PowerPivot, but USERNAME works from the beginning.
      LOGNM[DEPT] = D[DEPT] && LOGNM[USERID] = [mUserNm]
      ) > 0


  3. Raviteja G says:

    my below query throwing error syntax for IN is incorrect:
    Please let me know if i am missing anything.
    evaluate (
    ,”col1″ ,
    ,’sales’ row_id
    ,’sales’ col5
    ,’sales’ col6
    ), date in{“2018″,”2019″},segment=”ABC”, opp in {“1″,”2″,”10”}


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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s