Working with Inline Tables and Using IN Operator in Filter

It’s is often handy to be able to define a table in line with your calculation. Apparently, you can do it in DAX using the following syntax:

{ value1, value1, value3}

The statement above creates a table that can be used with a DAX function that expects a table as a parameter.

Example:

1. {1, 2, 2, 3, 3, 3} – a table of numbers
2. {“Chicago”, “Boston”, “Houston”} – a table of strings
3. COUNTROWS({1, 2, 2, 3, 3, 3 }) – returns 6
4. COUNTROWS({“Chicago”, “Boston”, “Houston”}) – returns 3

Note that when you create a measure that uses this syntax in Power BI Desktop, the editor will highlight some of it in red probably because the support for this is still relatively new.

Now if I want to write a calculation that uses IN operator, you can write something like that

CountTwos = CALCULATE([RowCount], Numbers[Value] IN {2})

This calculation will filter out values in the Numbers[Value] to be equal 2 (not a good example of IN operator so far 🙂 )

CountTwosAndThrees = CALCULATE([RowCount], Numbers[Value] IN {2, 3})

This calculation will filter out values in the Numbers[Value] to be equal 2 or 3.

Please see a sample Desktop file with some of examples of these calculations.