Where Condition in Power BI DAX

Trying to filter your data in a Power BI measure and looking for the DAX "WHERE" clause? You're in the right place, but the solution isn’t a direct command - it’s a different way of thinking that relies on a couple of incredibly powerful functions. This guide will walk you through exactly how to apply conditions in your DAX formulas using the most important filtering tools at your disposal.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Good news: There’s No “WHERE" Clause

If you have any experience with SQL, you live by the WHERE clause. It's how you tell the database precisely which rows of data you care about. When moving to Power BI's language, Data Analysis Expressions (DAX), it feels natural to look for its counterpart. But DAX doesn’t have a WHERE clause, and that's actually by design.

DAX is a functional language that operates differently. Instead of explicitly telling it which rows to filter in a query, you provide conditions inside functions that modify the "filter context." The filter context is just a fancy way of describing all the filters that are currently active on your data - filters from slicers, visuals, or other measures on your report.

The two primary functions you'll use to apply your own specific conditions, or your very own 'WHERE' clauses, are CALCULATE and FILTER.

The Two Most Important Functions for Conditions: CALCULATE and FILTER

While Power BI has dozens of functions, you can accomplish about 80% of your filtering needs by mastering just two:

• CALCULATE: The most versatile and frequently used function in all of DAX. It modifies the filter context to change how an expression is evaluated. It’s your go-to function for applying simple, direct filters.
• FILTER: A more specialized function that creates a temporary table based on a specific logical condition. It becomes necessary when your conditions are more complex and need to be evaluated row by row.

Let's walk through how to use each with some real-world examples. Imagine you have a simple Sales table with columns like [Product Category], [Region], [OrderDate], and [Sale Amount].

Applying Simple Conditions with CALCULATE

Think of CALCULATE as your Swiss Army knife. The first argument you give it is the expression you want to evaluate (like a sum, average, or count). Every argument after that is a filter condition that changes what data is included in that calculation.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

The basic syntax looks like this:

CALCULATE(<expression>, <filter1>, <filter2>, ...)

Each filter argument works as an implicit "AND." If you provide multiple filters, the calculation will only include data that meets all of the conditions.

Example 1: Total Sales for a Specific Region

Let's say you want to create a measure that always shows the total sales for the "North" region, regardless of what other slicers or filters are selected in your report. This is a perfect job for CALCULATE.

Your DAX formula would be:

North Region Sales = 
CALCULATE(
    SUM(Sales[Sale Amount]),
    Sales[Region] = "North"
)

Here’s what’s happening:

• The expression is SUM(Sales[Sale Amount]). This is the calculation we want to perform.
• The filter is Sales[Region] = "North". This is our condition. CALCULATE applies this filter, overriding any existing filters on the [Region] column to show only the sales from the North region.

You can drag this new measure onto a card visual, and it will always show the total for the North region's sales.

Example 2: Applying Multiple "AND" Conditions

Now, let’s dial it up. What if you need the total sales for the "North" region, but only for the "Electronics" product category? Since CALCULATE handles multiple filters as AND conditions by default, you add another comma-separated argument.

The DAX formula would look like this:

North Electronics Sales = 
CALCULATE(
    SUM(Sales[Sale Amount]),
    Sales[Region] = "North",
    Sales[Product Category] = "Electronics"
)

This measure now only includes sales data where the region is "North" and the category is "Electronics." Super simple and perfectly readable.

Handling Complex, Row-by-Row Conditions with FILTER

CALCULATE is perfect for simple conditions on single columns like [Region] = "North". But what if your condition relies on a calculation? For example, how would you find the total sales from "large orders" — let’s define that as any order over $1,000?

You can't just write Sales[Sale Amount] > 1000 as a direct filter in CALCULATE. Simple filters in CALCULATE are designed to look for fixed values in a column, not evaluate a changing condition row by row.

This is where the FILTER function shines. The FILTER function scans a table one row at a time and returns a new temporary table that only contains the rows that meet your specific condition. You then use this temporary, filtered table inside CALCULATE.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

The basic syntax for FILTER is:

FILTER(<table>, <logical_expression>)

Example: Total Sales from "Large" Orders

To calculate the revenue from sales over $1,000, you first need to generate a table containing only those sales. Here’s how you’d use FILTER to do it, wrapped inside CALCULATE to apply that condition to our SUM.

Large Order Sales = 
CALCULATE(
    SUM(Sales[Sale Amount]),
    FILTER(
        Sales,
        Sales[Sale Amount] > 1000
    )
)

Let’s break it down:

1. FILTER(Sales, Sales[Sale Amount] > 1000): This is the core logic. The FILTER function looks at the entire Sales table first. For each row, it checks if the value in the [Sale Amount] column is greater than 1000. It builds a mini-table filled only with the rows that pass this test.
2. CALCULATE(SUM(Sales[Sale Amount]), ...): The CALCULATE function then takes that temporary table of 'large orders' created by FILTER and calculates the sum of the [Sale Amount] column for just those rows.

The clear rule is: when your condition involves comparing a column to a fixed value (like "North," "Electronics," or a specific date), use a simple filter in CALCULATE. When your condition requires a calculation across rows (like price > cost or quantity > 10), wrap your logic in the FILTER function.

Creating OR Conditions with the || Operator

So far, we’ve covered AND logic. But what about 'OR' conditions? For example, what if you want to calculate the total sales for the "North" and "South" regions combined?

For this, you need to use the FILTER function with the OR operator, which in DAX is two vertical pipes: ||

North & South Region Sales = 
CALCULATE(
    SUM(Sales[Sale Amount]),
    FILTER(
        Sales,
        Sales[Region] = "North" || Sales[Region] = "South"
    )
)

In this formula, FILTER goes through the Sales table row by row and keeps any row where the region is either "North" or "South." CALCULATE then sums up the Sale Amount from this combined temporary table.

Pro Tip: As a shortcut for 'OR' conditions on the same column, you can also use the IN operator:

North & South Region Sales (Shortcut) =
CALCULATE(
    SUM(Sales[Sale Amount]),
    Sales[Region] IN {"North", "South"}
)

This version is cleaner and easier to read, but it achieves the exact same result.

Common Pitfalls and Best Practices

As you get comfortable with applying 'WHERE' conditions in DAX, keep these points in mind to avoid common headaches.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

1. Respecting the Filter Context

Remember that the filters you add in a CALCULATE statement work in addition to any filters coming from slicers or visuals on your report page unless your measure is specifically designed to ignore them (using functions like ALL). If your "North Region Sales" measure returns blank, it's likely because you also have a slicer set to "West," and the data can't be in both places at once.

2. Dates Deserve a Date Table

While you can filter dates directly in your sales table, the best practice in Power BI is to always have a dedicated Calendar table. A date table unlocks powerful time-intelligence functions and makes filtering by year, quarter, or custom date ranges much more straightforward.

For example, to find sales for Q1 2024, you can use:

Q1 2024 Sales = 
CALCULATE(
    SUM(Sales[Sale Amount]),
    DATESBETWEEN(
        Calendar[Date],
        DATE(2024, 1, 1),
        DATE(2024, 3, 31)
    )
)

3. Use Variables to Keep Your Formulas Clean

Once your conditional logic gets more involved, DAX can become difficult to read. Use variables (VAR) to break your calculations into logical steps. This makes your code cleaner and easier to debug.

Here’s our complex FILTER example rewritten with a variable:

Large High-Margin Orders = 
VAR LargeOrders = 
    FILTER(
        Sales,
        Sales[Sale Amount] > 1000 
            && Sales[Product Category] = "Electronics"
    )
RETURN
    CALCULATE(SUM(Sales[Sale Amount]), LargeOrders)

Final Thoughts

While DAX doesn't have a direct WHERE clause like SQL, its functional approach to filtering with CALCULATE and FILTER is incredibly powerful once you get the hang of it. By mastering these two functions, you can replicate almost any condition you’d build in SQL and unlock the full analytical potential of your Power BI reports.

Learning the syntax of DAX is often a big barrier for marketers, founders, and anyone who isn't a full-time data analyst. At our company, we believe getting insights shouldn't require learning a new query language. With Graphed, you can connect your data sources and simply ask questions in plain English — like "what were my total sales from large orders over $1000 in the North region last quarter?" — and get back an interactive dashboard, without writing a single line of DAX.