DAX Logical Operations: AND, OR, and IN Operators Explained
In this article, we will learn how to use logical operators in DAX to
filter data based on multiple conditions. We will explore the
AND (&&), OR (||), and
IN operators, along with practical examples using the
Product table.
📥 Practice Data:
Download Practice Data Free
🎥 Prefer learning through video?
DAX YouTube Playlist
What Are Logical Operators in DAX?
Logical operators allow us to combine conditions when filtering data.
They are especially useful when we want to retrieve products that match
multiple requirements or belong to one of several categories.
| Operator | Meaning | When to use it |
|---|---|---|
| && | AND | All conditions must be TRUE |
| || | OR | At least one condition must be TRUE |
| IN | Match against a list of values | Check whether a column contains one of several values |
1. Retrieving the Entire Product Table
Query
// Logical Operations
EVALUATE
'Product'Step-by-step explanation
Step 1: EVALUATE
The EVALUATE statement tells DAX to return a table
expression as the query result.
Step 2: ‘Product’
The ‘Product’ table is the table expression.
Since we are directly referencing the table, DAX returns its data.
What will you get?
The result contains the columns and rows available in the
Product table.
EVALUATE ‘Product’ is useful for viewing the source data
before applying filters.
2. Logical AND (&&): All Conditions Must Be TRUE
The AND operator (&&) is used when we want every
condition to be satisfied.
Business requirement
Get the red components whose
list price is less than 500.
Query
EVALUATE
FILTER(
'Product',
'Product'[Color] = "Red" && 'Product'[Category] = "Components" && 'Product'[List Price] < 500
)Step-by-step explanation
Step 1: FILTER(‘Product’, …)
The FILTER function starts with the
Product table and evaluates a condition for each row.
Step 2: ‘Product'[Color] = “Red”
The first condition checks whether the product color is
Red.
Step 3: ‘Product'[Category] = “Components”
The second condition checks whether the product category is
Components.
Step 4: ‘Product'[List Price] < 500
The third condition checks whether the list price is less than
500.
Step 5: Combine the conditions using &&
All three conditions must be TRUE for a row to be included in the result.
Understanding the AND condition
| Color | Category | List Price | Result |
|---|---|---|---|
| Red | Components | 450 | Included |
| Red | Accessories | 450 | Excluded |
| Blue | Components | 450 | Excluded |
| Red | Components | 600 | Excluded |
The values above are illustrative. The actual query result depends on
the data in your Product table.
With &&, every condition must be TRUE.
If even one condition is FALSE, the row is excluded.
3. Logical OR (||): Any One Condition Must Be TRUE
The OR operator (||) is used when we want to include
rows that satisfy at least one of several conditions.
Business requirement
Get all Components and Accessories,
or any items whose list price is less than 50.
Query 1: Check the Available Categories
EVALUATE
DISTINCT('Product'[Category])Step-by-step explanation
Step 1: DISTINCT(‘Product'[Category])
The DISTINCT function returns the unique values from
the Category column.
This helps us understand which category values are available before
applying the OR condition.
What will you get?
The result contains the distinct category values available in the
Product table.
DISTINCT is useful when you want to inspect unique values
in a column.
Query 2: Apply the OR Conditions
EVALUATE
FILTER(
'Product',
'Product'[Category] = "Components" || 'Product'[Category] = "Accessories" || 'Product'[List Price] < 50
)Step-by-step explanation
Step 1: FILTER(‘Product’, …)
The FILTER function evaluates the conditions against
the Product table.
Step 2: Category = “Components”
Includes products whose category is Components.
Step 3: Category = “Accessories”
Includes products whose category is Accessories.
Step 4: List Price < 50
Includes products whose list price is less than 50,
regardless of their category.
Step 5: Combine the conditions using ||
A row is included if at least one of the three conditions
is TRUE.
Understanding the OR condition
| Category | List Price | Result |
|---|---|---|
| Components | 450 | Included |
| Accessories | 600 | Included |
| Bikes | 40 | Included |
| Bikes | 200 | Excluded |
The values above are illustrative. The actual query result depends on
the data in your Product table.
With ||, a row is included when any one of the
conditions is TRUE.
4. Logical OR Within the Same Column Using IN
When we want to compare the same column against multiple values,
the IN operator makes the query shorter and easier
to read.
Business requirement
Get the products whose colors are Red,
Grey, or Silver.
Query 1: Using Multiple OR Conditions
EVALUATE
FILTER(
'Product',
'Product'[Color] = "Red" || 'Product'[Color] = "Grey" || 'Product'[Color] = "Silver"
)Step-by-step explanation
Step 1: FILTER(‘Product’, …)
Starts with the Product table.
Step 2: Compare the Color column
The query checks whether the color is Red,
Grey, or Silver.
Step 3: Combine the conditions using ||
A product is included if its color matches any one of the three values.
Query 2: Using the IN Operator
EVALUATE
FILTER(
'Product',
'Product'[Color] IN {"Red", "Grey", "Silver"}
)Step-by-step explanation
Step 1: FILTER(‘Product’, …)
Starts with the Product table.
Step 2: ‘Product'[Color]
Specifies the column whose value we want to check.
Step 3: IN
The IN operator checks whether the column value
matches any value in the specified list.
Step 4: {“Red”, “Grey”, “Silver”}
This is the list of allowed color values.
Comparing the two approaches
| Approach | Purpose |
|---|---|
| Color = “Red” || Color = “Grey” || Color = “Silver” | Checks each value using separate OR conditions |
| Color IN {“Red”, “Grey”, “Silver”} | Checks whether the value belongs to a list |
IN is a cleaner alternative when multiple OR conditions
compare the same column.
5. Combining AND, OR, and IN
Logical operators become especially useful when we need to combine
multiple business rules in a single filter.
Business requirement
Get all Components and Accessories
that are available in Red, Grey,
or Silver.
Query
EVALUATE
FILTER(
'Product',
('Product'[Category] = "Components" || 'Product'[Category] = "Accessories")
&& 'Product'[Color] IN {"Red", "Grey", "Silver"}
)Step-by-step explanation
Step 1: FILTER(‘Product’, …)
Starts with the Product table and evaluates the
combined condition.
Step 2: Category = “Components” || Category = “Accessories”
The first part checks whether the product belongs to either
Components or Accessories.
Step 3: Use parentheses
The parentheses group the OR conditions together.
This makes it clear that the category must be one of the two values.
Step 4: Color IN {“Red”, “Grey”, “Silver”}
The second part checks whether the color belongs to the specified list.
Step 5: Combine the two groups using &&
Both groups must be TRUE:
- The category must be Components or Accessories.
- The color must be Red, Grey, or Silver.
Understanding the combined condition
| Category | Color | Result |
|---|---|---|
| Components | Red | Included |
| Accessories | Silver | Included |
| Components | Blue | Excluded |
| Bikes | Red | Excluded |
The values above are illustrative. The actual query result depends on
the data in your Product table.
The query uses OR to group the allowed categories,
IN to match multiple colors, and
AND to require both groups to be satisfied.
Understanding the Difference Between AND, OR, and IN
| Operator | Example | Meaning |
|---|---|---|
| && | Color = “Red” && Price < 500 | Both conditions must be TRUE |
| || | Category = “Components” || Category = “Accessories” | At least one condition must be TRUE |
| IN | Color IN {“Red”, “Grey”, “Silver”} | Matches any value in the list |
Important Notes
1. Use AND when every condition is required
If the business requirement says that all conditions must be satisfied,
use &&.
2. Use OR when any condition is acceptable
If the business requirement allows any one of several conditions,
use ||.
3. Use IN for multiple values in the same column
Instead of writing multiple OR conditions for the same column,
you can use IN with a list of values.
4. Use parentheses when combining logical operators
Parentheses make the intended grouping clear, especially when combining
AND and OR.
5. FILTER returns a table
The FILTER function returns the rows that satisfy
the specified condition.
📝 Practice Exercise
Try writing a DAX query to retrieve products that satisfy the following
conditions:
- Category is Components or Accessories.
- Color is Red, Grey, or Silver.
- List Price is less than 500.
Expected Query
EVALUATE
FILTER(
'Product',
('Product'[Category] = "Components" || 'Product'[Category] = "Accessories")
&& 'Product'[Color] IN {"Red", "Grey", "Silver"}
&& 'Product'[List Price] < 500
)
This query combines OR, IN, and
AND to apply multiple filtering conditions.
Conclusion
In this article, we learned how to use logical operators in DAX
to filter data based on multiple conditions.
- && requires all conditions to be TRUE.
- || requires at least one condition to be TRUE.
- IN checks whether a value belongs to a list.
- Parentheses help group conditions when combining logical operators.
- FILTER returns the rows that satisfy the conditions.
These operators are essential for writing DAX queries that retrieve
specific subsets of data based on business requirements.
📥 Practice Data:
Download Practice Data Free
🎥 Prefer learning through video?
DAX YouTube Playlist