FILTER function in Power BI - Step-by-Step Guide

Introduction

The FILTER function helps you pick only the rows you want from a table based on a rule you set. This is useful when you want to focus on specific data, like sales from one region or dates in a certain year.

When you want to show sales only for the current year in a report.

When you need to create a list of customers who bought more than 100 items.

When your dashboard should display data only for a specific product category.

When you want to calculate totals but only for a certain region.

When you want to compare filtered data sets side by side.

Steps

Step 1: Open the Power BI Desktop file with your data model

- Power BI Desktop main window

You see your report canvas and data fields on the right

Step 2: Click on the Modeling tab

- Ribbon at the top

Modeling options appear, including New Measure and New Column

Step 3: Click New Measure

- Modeling tab

A formula bar appears at the top where you can type DAX

Step 4: Type a FILTER formula like: FilteredSales = CALCULATE(SUM(Sales[Amount]), FILTER(Sales, Sales[Region] = "West"))

- Formula bar

A new measure named FilteredSales is created that sums sales only for the West region

💡 Use double quotes for text values inside FILTER conditions

Step 5: Add a card visual to the report canvas

- Visualizations pane

A blank card appears on the canvas

Step 6: Drag the new measure FilteredSales to the card visual

- Fields pane to card visual

The card shows the total sales amount only for the West region

Before vs After

Before

A card visual shows total sales for all regions: 1,000,000

After

The card visual shows total sales only for the West region: 250,000

Settings Reference

FILTER function syntax

📍 DAX formula bar

Defines which rows to keep from the table based on the condition

Default: No default, you must specify table and condition

CALCULATE function

📍 DAX formula bar

Changes the context of calculation using filters like FILTER

Default: No default, expression and filters must be provided

Common Mistakes

Using single quotes instead of double quotes for text in FILTER condition

DAX requires double quotes for text values, single quotes cause errors

Use double quotes like Sales[Region] = "West"

Not wrapping FILTER inside CALCULATE when creating a measure

FILTER alone returns a table, but measures need a single value, so CALCULATE is needed to apply the filter

Use CALCULATE with FILTER to get a single value measure

Summary

FILTER lets you pick rows from a table based on a rule you set.

Use FILTER inside CALCULATE to create measures that show filtered results.

Remember to use double quotes for text and wrap FILTER in CALCULATE for measures.