Extended filter criteria in DAX: Using the IN-operator

If you regularly write queries in SQL, you will be familiar with the ability to use the IN-operator within the WHERE clause in SQL to filter your queries for specific values or for the result of subqueries.

Since November 2016 the IN-operator also is available in DAX and extends your possibilities in filtering table expressions in Power BI, Excel 2016 (from version 1701) or SSAS Tabular 2017.

In this article we take a look at how and where to use the IN-operator in DAX.

Let's start with an example to illustrate how the IN-operator works in SQL and DAX. Look at the following table, which shows the "Forbes" list of the Top 20 companies by sales worldwide for 2015:

The table on the left hand side is restricted to those companies belonging to "Germany" or "Switzerland" in the "Country"-column using the IN-operator. Below you can see the DAX- and the corresponding SQL-code for this restriction:

This example already shows a special feature of the IN-operator. The filter criteria "Germany" and "Switzerland" are passed to the table "Forbes2015" as OR conditions. Accordingly, all companies belonging to "Germany" or "Switzerland" are shown in the result set above. Conditions in DAX functions such as FILTER() or CALCULATETABLE() are usually concatenated using a logical AND:

In both examples the filter condition for the "Country"-column must be written explicitly. In the example to the left the classic parameter separator "," is used between the two filter conditions. Therefore an AND-linkage of the conditions is established.

This condition would lead to an empty result set because there is no company that belongs to both "Germany" and "Switzerland".
The code on the right hand side creates an OR-linkage between "Germany" and "Switzerland" by using the double pipe ("||") character. That's why the same result is achieved as in our original example. Even in this relatively short code example the IN-operator reduces the writing effort and increases readability of the code.

Similar to some other cases in DAX the IN-operator is not the only way to solve a specific problem. Basically the IN-operator is nothing more than "syntax sugar", a simplifying abbreviation for an alternative code expression. The IN-operator is a short notation for using CONTAINSROW(). Marco Russo has studied this aspect of the IN-operator in more detail. If you want to know more about it, you can find his article using the following link: The IN-operator in DAX.

To enable the use of the IN-operator in DAX, a syntax was introduced that was previously primarily known in Power BI from the M language for Power Query. The use of curly brackets ("{}").

Since this innovation, DAX has included so-called "table constructors" and "row constructors", which can be used to define "anonymous" tables, meaning tables that are not retrieved from physically existing tables in the data model.

The braces "{}" introduce the "table constructor" in which you can specify a list of values that represent the table contents. In the previous section these were the countries "Germany" and "Switzerland", that is {"Germany", "Switzerland"}, which created a single-column table with these entries.

To create a multi-column table you can also use the "row constructor", that is introduced by simple parentheses "()". The following example shows the syntax:

The code to the right shows how to create additional columns within the "table constructor" by using the "row constructor" to assign a business sector to each country.

Prefixing the keyword "IN" now allows you to use this syntax within boolean conditions, meaning true/false comparison operations, to filter your tables.

In addition to a "rigid" list of values it is also possible to use DAX expressions and column references of physically existing tables in "table constructors" or "row constructors" to determine the filter criteria:

However, please note that the DAX expression used always has to return one single value. The attempt to use the TOP 5 sales values from table "Forbes2015" in the "table constructor" to the right is acknowledged by DAX Studio with an error message while using a single value leads to successful code execution.

Up to now we only dealt with the filtering of a single column using the IN-operator. Of course you can also use the IN-operator to filter multiple columns. Therefore you have to use the syntax of a "row constructor" (blue area in the following example) and precede the IN-operator and a list of the columns to be filtered (green highlighted area):

The example shows that you have to follow a series of syntactical rules in order to filter your table to the desired result. In detail these seem to be:

The example "Incorrect column order" violates the fourth rule. The column "Sector" is positioned before the column "Country" in the output-table. Accordingly, the "Incorrect column order" leads to an empty result set, whereas the "Correct column order" leads to the desired result. As described above individual "row constructors" are linked by a logical OR while the criteria in the columns of each "row constructor" is linked by a logical AND. In simple terms, the above code might read something like this:

"Filter the table 'Forbes2015' for the data records to be assigned to the country 'United States' and the sector 'Energy' or the country 'China' and the sector 'Energy' "

To check the AND concatenation, e.g. "Country"=China and "Sector"="Energy", when filtering several columns, access to the row context of the table to be filtered is required. This is created by using FILTER() as an iterator. In this case the usage of CALCULATETABLE() instead of FILTER() won't get the job done.

You can use the IN-operator to filter both one column and several columns. Similar to the IN-operator in SQL you can not only filter for the data that matches your criteria, but also for all data that doesn't match the selected criteria. You can do this by using the NOT() function:

The green highlighted area shows the same filter condition that we already got to know in the last section. This is now enclosed by the function NOT() (highlighted in yellow), which reverses the filter logic and thus generates the result to the right, in which all records are shown that don't meet the specified criteria. The easiest way to check is to use the "Rank" column in which the entries with the rank 3, 4, 6, 11 and 17 are missing and thus exactly those records that formed the result in the previous section.

In addition we have always used fixed value lists as filter criteria in the IN-operator. Of course it's also possible to transfer the criteria from a table expression. This is always useful if the contents of other tables are to be used as filter criteria in your DAX statement. A separate table constructor no longer is necessary in such a scenario:

The "Sector"-column is read from the table "FilterKriterien" by using the table function VALUES() and used to filter our table "Forbes2015". In the second example the entries from the "FilterSector"- and "FilterCountry"-columns of the table "FilterCriteria" are queried using SELECTCOLUMNS() and injected to "Forbes2015". Accordingly, the first result set above shows all companies in the "Energy" or "Financials" sectors, while the entries corresponding to the criteria "Energy" and "France" or "Financials" and "United States" are displayed in the second below.

Basically, you can use the IN-operator whenever you perform boolean comparison operations. In our examples we mainly used it for filtering table expressions created with FILTER().

However, it is also possible to use the IN-operator in comparison operations in logical branches, such as IF() or SWITCH(), because true/false comparisons are also performed there. Soheil Bakhshi uses the IN-operator within SWITCH()-function to create new product groups in a calculated column in Power BI. Soheils blogpost can be found here. .

Another application area for the IN-operator could also be your row-level security (RLS) rules in Power BI or SSAS Tabular. The criteria and conditions for data access defined in RLS in some cases might be much more understandable by using the IN-operator.

I hope I was able to give you a basic understanding of how to use the IN-operator in DAX. Do you like the topic? Did I miss to mention an important aspect? Leave me a comment below and share if you liked it.

Regards from Germany,

Uwe