Power BI DAX FILTER: A Practical Guide With Examples

A sales manager opens a monthly dashboard and notices something confusing. Total sales look right, but the “High-Value Orders” card shows every order, not only orders above the agreed threshold. I have seen this often when teams move from Excel reports into Power BI Desktop.

The report already has clean data from Excel, SharePoint, or SQL Server. The missing piece is a way to tell a calculation exactly which rows matter. That is where Power BI DAX FILTER becomes useful.

This guide walks through how to use the FILTER function in a practical retail sales dashboard, including common patterns, measures, visuals, and mistakes to avoid.

What Is Power BI DAX FILTER?

FILTER is a DAX function that returns a table containing only the rows that meet a condition. DAX stands for Data Analysis Expressions, the formula language that powers calculations in Power BI.

Unlike a visual-level filter or a slicer, FILTER works inside a formula. It lets you define custom logic for a Measure, which is a calculation that changes based on the current filters in your report.

The syntax looks like this:

FILTER(<table>, <filter_expression>)

• <table> is the table that you want to examine.
• <filter_expression> is the condition that each row must satisfy.

For example, imagine a Sales table with these columns:

• Order Date
• Product
• Region
• Salesperson
• Amount
• Quantity

This formula returns sales rows where the order amount exceeds 1,000:

FILTER(
    Sales,
    Sales[Amount] > 1000
)

By itself, FILTER returns a table, not a single number. That is why you will usually use it inside another DAX function, especially CALCULATE.

For a wider introduction to DAX concepts before building more advanced formulas, read this guide on Power BI DAX.

Why Use Power BI DAX FILTER?

A standard report filter works well when every visual needs the same rule. However, real dashboards often need one visual to follow a special business rule while other visuals keep showing all data.

For example, a retail company may want these KPIs on one sales dashboard:

• Total sales from every completed order.
• Sales from the West region only.
• Revenue from orders above 1,000.
• Revenue from orders created in the last 30 days.
• Sales from products with a margin greater than 25 percent.

You could create separate pages or apply visual filters. But that approach becomes difficult to maintain when users need several calculations together in a single interactive report.

Power BI DAX FILTER gives each measure its own rule. Your total sales card can show all sales, while a second card shows only high-value sales. Both cards can still respond to the same Year, Region, and Product Slicers.

Pro Tip: I have found that measures with clear business names save a lot of support time later. Name a measure High Value Sales instead of Measure 7, and document the threshold in the formula or model description.

Build the Sales Data Model First

Before writing DAX, set up a clean Data model. In client projects, I usually start with a simple star schema. A star schema keeps one large transaction table in the center and connects it to smaller lookup tables.

For this example, use these tables:

• Sales: Fact table with one row per sales order line.
• Date: Calendar table with one row per date.
• Products: Product name, category, and brand.
• Customers: Customer name, segment, and city.
• Regions: Region and sales territory details.

The Sales table sits in the center. Create one-to-many relationships from the lookup tables to Sales:

• Date[Date] to Sales[Order Date]
• Products[Product ID] to Sales[Product ID]
• Customers[Customer ID] to Sales[Customer ID]
• Regions[Region ID] to Sales[Region ID]

Use a single filter direction from each lookup table to the Sales table. This design makes DAX measures easier to understand and helps prevent incorrect totals.

You can also learn how to work with relationships when unique values are unavailable in this guide about creating a relationship in Power BI without unique values.

Create and Mark a Date Table

A separate Date table gives your report a reliable calendar for monthly, quarterly, and yearly analysis. It also supports time-intelligence calculations.

In Power BI Desktop, select Modeling > New table, then enter:

Date =
CALENDAR(
    DATE(2023, 1, 1),
    DATE(2026, 12, 31)
)

Add supporting columns:

Year = YEAR('Date'[Date])

Month Number = MONTH('Date'[Date])

Month Name = FORMAT('Date'[Date], "MMMM")

Next, select the Date table, choose Table tools > Mark as date table, and select the Date column. Then sort Month Name by Month Number so months appear in calendar order instead of alphabetical order.

If you need a user-friendly date selection control, this article explains how to create a Power BI date slicer.

Create the Base Measure

Always create a base measure before adding complex filters. In this sales dashboard, the basic measure is total sales:

Total Sales =
SUM(Sales[Amount])

This Measure adds the values in Sales[Amount]. It automatically responds to filters from visuals, slicers, the report page, and related tables.

Add Total Sales to a Card visual. Then add a clustered column chart with Products[Category] on the X-axis and Total Sales on the Y-axis. Add a line chart with Date[Month Name] and Total Sales to show the sales trend.

A well-designed measure makes future formulas much shorter. Instead of repeating SUM(Sales[Amount]) in every formula, you reuse [Total Sales].

This is also why a Measure differs from a calculated column. A calculated column stores a value for every row during refresh, while a measure calculates results only when users view the report. See this detailed explanation of the difference between a calculated column and measure in Power BI.

Power BI DAX FILTER With CALCULATE

CALCULATE changes the filter context of a calculation. Filter context means the set of rows that Power BI considers when it calculates a measure.

The structure is:

CALCULATE(
    <expression>,
    <filter>
)

To calculate sales for orders above 1,000, create this measure:

High Value Sales =
CALCULATE(
    [Total Sales],
    FILTER(
        Sales,
        Sales[Amount] > 1000
    )
)

Here is what happens:

1. [Total Sales] tells Power BI what to calculate.
2. FILTER(Sales, Sales[Amount] > 1000) keeps only rows where the amount is greater than 1,000.
3. CALCULATE evaluates Total Sales using those filtered rows.

Add this measure to a second Card visual beside Total Sales. A manager can now compare overall revenue with revenue from high-value orders.

The formula remains interactive. If the manager selects the South region in a Slicer, both the card and the formula only consider South region sales. Then the FILTER condition further keeps orders above 1,000.

For additional examples with multiple conditions, review this guide on counting data with multiple filter conditions in Power BI DAX.

Filter Rows With Multiple Conditions in Power BI

You can combine several conditions with the && operator, which means “and.” Suppose your sales director wants revenue from high-value Technology orders:

High Value Technology Sales =
CALCULATE(
    [Total Sales],
    FILTER(
        Sales,
        Sales[Amount] > 1000 &&
        RELATED(Products[Category]) = "Technology"
    )
)

This formula checks each row in Sales. It keeps the row only when the amount exceeds 1,000 and the related product category equals Technology.

RELATED retrieves a value from a related lookup table. It works well here because Products has a relationship with Sales.

If your condition uses columns from only one table, a simpler Boolean filter often works:

West Region Sales =
CALCULATE(
    [Total Sales],
    Regions[Region Name] = "West"
)

Use the simpler filter when possible. Use FILTER when your logic needs row-by-row evaluation, involves a measure, uses more complex comparisons, or needs functions such as RELATED.

Filter Sales by Date in Power BI

Date-based filters are among the most common uses of Power BI DAX FILTER. A sales manager often asks, “How much did we sell in the last 30 days?” or “What did this year’s revenue look like?”

Create a measure for sales in the last 30 days:

Sales Last 30 Days =
CALCULATE(
    [Total Sales],
    FILTER(
        ALL('Date'[Date]),
        'Date'[Date] > TODAY() - 30 &&
        'Date'[Date] <= TODAY()
    )
)

ALL(‘Date'[Date]) removes the existing filter from the Date column before the formula applies its own 30-day date range. Without it, a Month slicer might limit the available dates and create unexpected results.

Then FILTER keeps dates from the past 30 days through today. Power BI sends that date filter through the relationship to the Sales table.

Use this measure in a card, and place it next to a card for monthly sales. It gives leaders a rolling view that does not reset when a new month begins.

If date logic does not behave as expected, start with this guide on fixing a Power BI DAX date filter that is not working. For other rolling-period examples, see how to filter last N days of data using Power BI DAX.

Compare Current Sales to Last Year in Power BI

A proper Date table also makes year-over-year comparisons simple:

Sales LY =
CALCULATE(
    [Total Sales],
    SAMEPERIODLASTYEAR('Date'[Date])
)

SAMEPERIODLASTYEAR shifts the current date selection back one year. If a user selects March 2026, the measure returns sales for March 2025.

Create a variance measure:

Sales Variance =
[Total Sales] - [Sales LY]

Create a percentage measure:

Sales Growth % =
DIVIDE(
    [Sales Variance],
    [Sales LY],
    0
)

Format Sales Growth % as a percentage. Add Total Sales, Sales LY, and Sales Growth % to cards. Then use conditional formatting to show positive growth in green and negative growth in red.

For a separate practical example, explore month-over-month change in Power BI.

Use FILTER to Ignore a Visual Selection

Sometimes a measure must keep one filter but ignore another. For example, a category bar chart should show each category’s sales as a percentage of the selected region total.

Start with this measure:

Category Sales % of Region =
DIVIDE(
    [Total Sales],
    CALCULATE(
        [Total Sales],
        ALL(Products[Category])
    ),
    0
)

ALL(Products[Category]) removes the category filter only. It does not remove filters for Region, Year, or Customer Segment. This makes the denominator the total sales for the current region and date selection.

Now consider a stricter example. Your sales team may need sales that ignore the Product slicer but still respect the Region slicer:

Sales Ignoring Product Selection =
CALCULATE(
    [Total Sales],
    REMOVEFILTERS(Products)
)

REMOVEFILTERS clearly states that you want to remove filters from a table or column. It often reads better than ALL when the goal is filter removal.

Choose these patterns carefully. Removing too many filters can produce a number that users cannot explain. Use a clear visual title such as “Share of Selected Region Sales” so people understand what the KPI represents.

For more examples, see this guide on how to remove filters in Power BI DAX.

Build the Interactive Report in Power BI

Now turn the measures into a useful sales dashboard. I usually create one executive summary page first, rather than placing every available visual on the canvas.

Use this layout:

• Add three Card visuals for Total Sales, High Value Sales, and Sales Growth %.
• Add a line chart showing Total Sales by month.
• Add a clustered bar chart showing Total Sales by product category.
• Add a matrix with Region, Salesperson, Total Sales, High Value Sales.
• Add slicers for Year, Region, Product Category, and Customer Segment.

Keep the cards at the top because managers often need answers within seconds. Use the line chart to show direction over time, then use the category chart and matrix to explain the reason behind the trend.

When the report looks correct, publish it to a Power BI Service workspace. A workspace is a shared online area where a team can manage reports, dashboards, datasets, and access.

Before sharing, test each slicer with your DAX measures. Select a single region, multiple categories, and a date range. Check whether the business result matches the intended definition.

Pro Tip: In my experience, a measure is only complete after someone tests it with unexpected slicer combinations. Most DAX issues come from filter context, not from spelling errors in the formula.

Key Points

• Use a star schema: Keep dimension tables such as Date, Products, and Regions separate from the Sales fact table. Clear relationships make filter flow predictable.
• Create base measures first: Reuse measures such as [Total Sales] inside other DAX measures. This keeps formulas shorter and easier to maintain.
• Avoid unnecessary FILTER calls: A direct filter inside CALCULATE usually performs better when the condition only compares a column to a fixed value.
• Check blank values: Blank dates, missing region IDs, and blank amounts can change results. Review data quality in Power Query before blaming the DAX formula.
• Use Row-Level Security carefully: Row-Level Security restricts data for specific users. Test DAX measures under every security role before publishing sensitive sales data.
• Make business rules visible: Include the threshold in a visual title or tooltip. Users should know whether “High Value” means orders above 1,000 or another amount.

Frequently Asked Questions

Power BI DAX FILTER lets you create focused, reliable measures for high-value orders, rolling sales periods, and custom KPI logic. Start with a clean data model and reusable base measures, then use CALCULATE and FILTER only where the business rule truly needs them. I hope you found this article helpful.

You may also like

• Power BI DAX Filter IF
• SELECTEDVALUE with Power BI DAX FILTER
• Filter blank values in Power BI
• Create a dropdown slicer in Power BI
• Set up Row-Level Security in Power BI
• Create a report in Power BI Desktop