How to Use “NOT IN” Operator in Power BI (With Example)


You can use the following syntax in DAX to use a “NOT IN” operator:

filtered_data = 
CALCULATETABLE('my_data', NOT('my_data'[Team] IN {"A", "C"}))

This particular example creates a new table named filtered_data that only contains the rows from the table named my_data where the value in the Team column is not equal to A or C.

Note that we used curly brackets { } when specifying the values in our list.

The following example shows how to use this syntax in practice.

Example: How to Use “NOT IN” Operator in Power BI

Suppose we have the following table in Power BI named my_data that contains information about basketball players on various teams:

Suppose we would like to create a new table that only contains players who are not on teams A or C.

To do so, click the Table tools tab and then click the New table icon:

Then type in the following formula into the formula bar:

filtered_data = 
CALCULATETABLE('my_data', NOT('my_data'[Team] IN {"A", "C"}))

This will create a new table named filtered_data that only contains players who are not on teams A or C:

Power BI not in

Note that you can also use multiple NOT IN filters if you’d like.

For example, you could create a new table where the following conditions are both true:

  • The value in the Team column is not A or C
  • The value in the Position column is not Guard or Center

You can use the following syntax to do so:

filtered_data = 
CALCULATETABLE('my_data', NOT('my_data'[Team] IN {"A", "C"}) &&
                          NOT('my_data'[Position] IN {"Guard", "Center"}))

This will create a new table named filtered_data that only contains players who are not on teams A or C and who also do not have a position of Guard or Center:

Power BI multiple NOT IN filters

Note: You can find a complete list of operators you can use in DAX here.

Additional Resources

The following tutorials explain how to perform other common tasks in Power BI:

How to Count Number of Occurrences in Power BI
How to Write an IF Statement in Power BI
How to Write an IF Statement with Multiple Conditions in Power BI

Leave a Reply

Your email address will not be published. Required fields are marked *