Working with Inline Tables and Using IN Operator in Filter

No comments

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.

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