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.

The basic principle of 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:

Forbes 2015, DAX, IN-Operator, Power BI, Power Pivot, SSAS
Forbes 2015: Top 20 companies by sales

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:

IN-Operator, DAX, SQL, Power BI, Power Pivot, SSAS

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:

DAX, AND, OR, IN-operator

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.

"Table-" and "Row-Constructors" for creating anonymous tables

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:

Table-Constructor, Row-Constructor, In-Operator, DAX, Power BI, SSAS, Power Pivot

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:

Expressions, Table-Constructors,  Row-Constructors,DAX, IN-Operator, SSAS, Power BI, Power Pivot

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.

Filtering multiple columns using the IN-operator

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):

IN-Operator, DAX, Power BI, SSAS, Power Pivot

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:

  1. The number of columns in the "row constructor" of the anonymous table (blue area) needs to correspond to the number of columns to be filtered (green area).
  2. The filter criteria within the columns of the "row constructor" (blue area) needs to have the same order as the columns to be filtered (green area). So e.g. first "Sector" and afterwards "Country".
  3. The data types of the criteria in the "row constructor" (blue area) needs to be compatible with those of the columns to be filtered (green area).
  4. The order of the columns to be filtered (green area) needs to correspond to the order in which the columns occur in the source table (yellow area).

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.

What else does the IN-operator offer?

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:

DAX, Power BI, PowerPivot, IN-Operator, NOT IN

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:

DAX, Power BI, Power Pivot, IN-Operator, Table expressions

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.

Where else can you utilize the IN-operator?

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

BESTENS INFORMIERT
Entwickle Dein Know-How und Deine Möglichkeiten im Reporting
und erhalte mit dem Newsletter exklusive Beispieldateien
zu unseren Artikeln

BROWSE data-insights.de...

RSS-FEED BI BLOG...


Kommentar schreiben

Kommentare: 0