DAX FILTER in Power BI: Complete Guide with Real-World Examples

A sales manager once asked me why her monthly sales dashboard showed the same total even after she added a “high-value order” filter. Her Excel file had the data, and the Power BI visuals looked good, but the report logic did not match the business question.

This happens often when teams move from Excel reports to Power BI Desktop. A visual-level filter may work for one chart, but a proper DAX FILTER table formula gives you reusable logic across cards, charts, tables, and KPIs.

In this guide, I will show how to use a Power BI DAX FILTER table with practical sales-dashboard examples, including filtered measures, filtered tables, multiple conditions, dates, and related tables.

What Is a Power BI DAX FILTER Table?

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

The basic syntax looks like this:

FILTER(<table>, <filter_expression>)

• <table> is the table that you want to filter.
• <filter_expression> is the logical test that each row must pass.

For example, assume you have a table named Sales with columns such as Order ID, Order Date, Region, Product, Quantity, and Amount.

FILTER(
    Sales,
    Sales[Amount] > 1000
)

This formula returns only sales rows where the amount is greater than 1,000.

On its own, FILTER returns a table, not a single number. That is why you will commonly use it inside functions such as CALCULATE, CALCULATETABLE, SUMX, COUNTROWS, and AVERAGEX.

If you are still getting comfortable with DAX formulas, start with this detailed guide on Power BI DAX functions. It helps you understand how measures and calculations work inside a report.

Build the Sample Sales Data Model

Before writing a Power BI DAX FILTER table formula, build a clean data model. A data model defines how tables connect through relationships.

For this example, I will use a mid-sized retail company that sells office supplies across India. The business wants an interactive sales dashboard for regional managers.

The report uses these tables:

• Sales: Order-level transaction data from an Excel file or SQL Server database.
• Products: Product name, category, brand, and unit cost.
• Customers: Customer name, segment, city, and state.
• Date: A dedicated calendar table for time intelligence calculations.

The Sales table acts as the fact table because it stores transactions. The Products, Customers, and Date tables act as dimension tables because they describe the sales records.

Create one-to-many relationships from each dimension table to the Sales table:

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

Use single-direction filtering from dimensions to Sales unless you have a clear business reason for bidirectional filtering. This keeps the model easier to manage and avoids unexpected DAX results.

For date-based reporting, create a calendar table.

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

Then add useful columns:

Year = YEAR('Date'[Date])

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

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

After creating it, select the Date table, go to Table tools, choose Mark as date table, and select the Date column.

Pro Tip: In my experience, most DAX date-filter issues come from using the order date directly in visuals instead of a proper Date table. I create the Date table early, before adding any time-based measures.

Create the Core Sales Measure

Start with a simple Measure. A measure calculates values based on the current filter context, which means it changes when someone selects a slicer, clicks a chart, or filters the page.

Create this measure in the Sales table:

Total Sales =
SUM(Sales[Amount])

This formula adds every value in the Amount column. Add it to a Card visual to show total revenue.

For the retail sales dashboard, place these visuals on the first page:

• A Card for Total Sales
• A Card for Total Orders
• A Clustered column chart for sales by region
• A Line chart for monthly sales
• A Bar chart for sales by product category
• Slicers for year, region, category, and customer segment

A slicer lets report users narrow down data themselves. For example, a regional manager can select “South” and see only South-region sales.

You can also learn how to set up flexible filtering using Power BI DAX filter with SELECTEDVALUE. It is especially helpful when a slicer selection needs to control a measure.

Use FILTER With CALCULATE

The most common use of a Power BI DAX FILTER table is inside CALCULATE.

The CALCULATE function changes the filter context of a measure. In simple terms, it says: “Calculate this value, but use these rules first.”

Here is the syntax:

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

Suppose the sales director wants to track only high-value orders, where each sale exceeds 1,000.

Create this measure:

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

This measure works in three parts:

1. [Total Sales] tells Power BI what to calculate.
2. FILTER(Sales, Sales[Amount] > 1000) creates a temporary filtered table.
3. CALCULATE applies that filtered table before calculating total sales.

Put High Value Sales in a card next to Total Sales. The difference tells managers how much revenue came from larger transactions.

This is more useful than manually filtering a visual because you can reuse the measure in every visual. A line chart can show monthly high-value sales, and a bar chart can compare high-value sales across regions.

If you need to remove report filters for a specific measure, review how to remove filters in Power BI DAX. That technique often works alongside FILTER when you need controlled comparisons.

Filter a Table With Multiple Conditions

Business rules often need more than one condition. For example, the retail company may want to measure sales from the South region where the order amount is above 1,000.

Create this measure:

South High Value Sales =
CALCULATE(
    [Total Sales],
    FILTER(
        Sales,
        Sales[Region] = "South"
            && Sales[Amount] > 1000
    )
)

The && operator means AND. Every row must match both conditions:

• The Region must equal South.
• The Amount must exceed 1,000.

If you want to include either condition, use ||, which means OR.

South or High Value Sales =
CALCULATE(
    [Total Sales],
    FILTER(
        Sales,
        Sales[Region] = "South"
            || Sales[Amount] > 1000
    )
)

This formula includes all South sales, plus every sale over 1,000 from any other region.

For most simple conditions, you can write the filter directly in CALCULATE:

South Sales =
CALCULATE(
    [Total Sales],
    Sales[Region] = "South"
)

This version is shorter and easier to read. Use FILTER when your condition needs more logic, refers to a measure, uses row-by-row evaluation, or needs a table expression.

For more targeted scenarios, see Power BI DAX filter based on condition.

Filter Sales Using a Measure

This is where FILTER becomes particularly valuable.

Imagine the sales team defines a profitable order as one with at least 25 percent margin. First, create a measure for total profit.

Total Profit =
SUM(Sales[Profit])

Next, create a margin measure:

Profit Margin =
DIVIDE(
    [Total Profit],
    [Total Sales],
    0
)

The DIVIDE function safely handles divide-by-zero cases. The final 0 tells Power BI to return zero if Total Sales equals zero.

Now you may want to calculate sales only for products with a profit margin above 25 percent.

High Margin Product Sales =
CALCULATE(
    [Total Sales],
    FILTER(
        VALUES(Products[Product ID]),
        [Profit Margin] > 0.25
    )
)

This measure first gets the visible product IDs through VALUES(Products[Product ID]). Then FILTER checks the Profit Margin for each product. Finally, CALCULATE adds sales only for the products that pass the test.

This approach works well in a product profitability dashboard. Add a table visual with Product Name, Total Sales, Total Profit, and Profit Margin. Then add a bar chart using High Margin Product Sales by category.

Pro Tip: I avoid filtering a large transaction table when the business rule really belongs at the product level. Filtering the smaller Products table usually improves performance and makes the calculation easier to maintain.

Create a Filtered Calculated Table

Sometimes you need a new table, not a measure. A calculated table stores data in the model when the dataset refreshes.

For example, the sales operations team may need a separate table that contains only high-value orders for review.

Go to Modeling and select New table. Then enter this formula:

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

Power BI creates a new table named High Value Orders. You can use it in a table visual, export it, or build a dedicated review page.

You can also use CALCULATETABLE when you prefer a clear filtered-table structure:

South Region Orders =
CALCULATETABLE(
    Sales,
    Sales[Region] = "South"
)

Use calculated tables carefully. They refresh only when the dataset refreshes, so they do not react to slicers like measures do.

For interactive reporting, measures usually offer a better solution. Use calculated tables when you need a stable subset of rows in your model.

If you need to build a separate table from existing data, this guide on creating a table from another table in Power BI is a useful next step.

Filter Data by Date Range

Date filters are common in sales reports. A manager may want sales from the last 30 days, the current year, or a specific quarter.

Here is a measure for current-year sales:

Current Year Sales =
CALCULATE(
    [Total Sales],
    FILTER(
        'Date',
        'Date'[Year] = YEAR(TODAY())
    )
)

This formula filters the Date table to the current year. Because the Date table has a relationship with Sales, the filter flows to the Sales table automatically.

For the last 30 days, use this measure:

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

The ALL('Date'[Date]) part removes the existing filter on the Date column before Power BI applies the last-30-days range.

Use this measure in a card or line chart. It gives sales managers a rolling view of performance, even when the report includes an overall date slicer.

For detailed date examples, check how to filter data by date in Power BI DAX and how to filter the last N days in Power BI DAX.

Use FILTER Across Related Tables

A common challenge appears when the condition sits in a related table.

For example, the company wants to calculate sales for products in the Furniture category. Because Category exists in the Products table, write the measure like this:

Furniture Sales =
CALCULATE(
    [Total Sales],
    FILTER(
        Products,
        Products[Category] = "Furniture"
    )
)

The relationship between Products and Sales lets the filter travel from Products to Sales.

You can use the same approach for customer segments:

Corporate Customer Sales =
CALCULATE(
    [Total Sales],
    FILTER(
        Customers,
        Customers[Segment] = "Corporate"
    )
)

This design keeps your DAX cleaner because you filter the table that owns the column. Avoid copying category and segment values into the Sales table just to make calculations easier.

If you need a grouped result with filters, explore Power BI DAX GROUPBY with FILTER. It is useful for advanced summary tables.

Add Filtered Measures to a Dashboard

After building the measures, turn them into an interactive report.

For the retail sales dashboard, I would use this layout:

• Add a Card for Total Sales.
• Add a Card for High Value Sales.
• Add a Card for Sales Last 30 Days.
• Add a Line chart with Date[Month Name] on the X-axis and Total Sales on the Y-axis.
• Add a Clustered bar chart with Region on the Y-axis and High Value Sales on the X-axis.
• Add a Table visual with Product Name, Total Sales, Profit Margin, and High Margin Product Sales.
• Add Slicers for Year, Region, Category, and Customer Segment.

Sort Month Name by Month Number so months appear in calendar order. Select Month Name, choose Column tools, select Sort by column, and choose Month Number.

Once you finish the report, publish it from Power BI Desktop to a workspace in Power BI Service. A workspace is a shared area where your team stores, manages, and shares reports and datasets.

If your team uses SharePoint for its intranet, you can also embed a Power BI report in SharePoint Online for easier access.

Useful Tips

• Use a star schema: Keep Sales as the fact table and use separate Date, Product, and Customer dimension tables. A clean model makes DAX filters more predictable.
• Prefer measures for interactive KPIs: Measures respond to slicers and visual selections. Calculated tables only update during dataset refresh.
• Avoid unnecessary FILTER calls: Use direct CALCULATE filters for simple column conditions. Reserve FILTER for advanced row-by-row logic.
• Filter the smallest relevant table: Filter Products or Customers instead of a large Sales table when the condition belongs there.
• Use measures instead of calculated columns: Create measures for totals, margins, and KPIs. Calculated columns increase model size and do not respond to report context.
• Protect sensitive data: Apply Row-Level Security in Power BI when regional managers should only see their own sales territory.

Frequently Asked Questions

A Power BI DAX FILTER table gives you a reliable way to turn business rules into reusable calculations for a sales dashboard. Start with a clean data model, create base measures first, and use FILTER inside CALCULATE only when the logic requires it. I hope you found this article helpful.

You may also like:

• Power BI DAX filter with IF examples
• Count data with multiple filter conditions in Power BI
• Power BI DAX DISTINCTCOUNT with filter examples
• Add a date slicer in Power BI
• Create a measure table in Power BI