IN DAX operator in Power BI

The IN operator in DAX (Data Analysis Expressions) within Power BI is a powerful tool for simplifying expressions that check if a value exists within a list of values. It essentially provides a more concise way to write multiple OR conditions.

Purpose: • The IN operator allows you to test whether a value in a column matches any value in a specified list.
• It's particularly useful for filtering data or creating calculated columns/measures based on multiple possible values.

Basic Syntax: column IN {value1, value2, ..., valueN}
• column: The column you want to check.
• {value1, value2, ..., valueN}: A list of values to compare against, enclosed in curly braces {}.

How it Works: • The IN operator returns TRUE if the value in the specified column matches any of the values in the provided list.
• Otherwise, it returns FALSE.

Key Considerations: • Replacing OR Conditions: The IN operator effectively replaces multiple OR conditions, making DAX code more readable and easier to maintain.
• Table Constructors: The curly braces {} are used to create what's known as a "table constructor," which defines a single-column table of values.
• Negation: To negate the IN operator (i.e., check if a value is not in the list), you use the NOT() function: NOT(column IN {value1, value2, ..., valueN}).

Example: Let’s create a calculated column in Power BI.

DAX

  IsSpecialCountry = IF(
    'Students List'[Country] IN {"New Zealand", "Brazil", "America"},
    "Yes",
    "No"
)

The output of the above dax code is shown below:

Explanation:

For each row:

If 'Students List'[Country] is "New Zealand", "Brazil", or "America", it returns "Yes".
Otherwise, it returns "No".

Equivalent to: 'Students List'[Country] = "New Zealand" || 'Students List'[Country] = "Brazil" || 'Students List'[Country] = "America".

Previous Next