Guides

CALCULATETABLE vs FILTER in Power BI DAX

By Ravindra Bagale, Power BI & Data Analytics trainer · LinkedIn · Published: 30 September 2026 · Last updated: 30 September 2026

CALCULATETABLE returns a table in a new filter context, the way CALCULATE returns a number. FILTER walks a table and keeps rows that pass a test — usually a measure test on a short list, not every fact row.

CALCULATETABLE ( Sales, 'Date'[Year] = 2025 )
COUNTROWS ( FILTER ( VALUES ( Customer[City] ), [Total Sales] > 100000 ) )

Why each one, and the rows-in number-out path

Four sales rows:

Why CALCULATE when you want a number:

1. "Sales in 2025" is one number.
2. The year test is a filter. It keeps 2025 rows (100, 40, 80).
3. SUM walks those rows. Number out: 220.
4. You do not need the FILTER function for that. CALCULATE ( [Total Sales], 'Date'[Year] = 2025 ) is the column filter.

Why CALCULATETABLE:

1. Same filter, but you need the rows themselves, not the sum.
2. It returns a table: the three 2025 rows.
3. A card cannot show a table. Wrap with COUNTROWS if the card should say 3, or use CALCULATE if the card should say 220.

Why FILTER:

1. "How many cities have sales above 100?" The test is a measure, not a column value on one row.
2. VALUES ( Customer[City] ) is a short list: Pune, Nashik.
3. FILTER keeps a city only when [Total Sales] for that city clears the bar.
4. COUNTROWS turns the kept list into one number.
5. FILTER ( Sales, Sales[Amount] > 50 ) also works, but it walks every fact row. Prefer it only when the test really needs the row. For "year = 2025" or "channel = Online", stay with the column filter.

What to check on the page: change the year slicer. A measure that replaced the year will ignore you. A measure that only counts cities inside the slicer will move.

Real example: 2025 slips, and bills over 500

Phone bills for the Mumbai shop: Jan 499, Feb 499, Mar 699. Pune's bill is 399 every month.

Why CALCULATE for a number:

1. Question: "2025 sales."
2. The year filter keeps A, B and C. The 2024 slip stays out.
3. Rows in: three slips. SUM out: 800. That is a card. No FILTER function required, because Year is a column.

Why CALCULATETABLE:

1. Same year filter, but you want the slips themselves (a table), not 800.
2. A card still cannot show those rows. COUNTROWS of that table out: 3.

Why FILTER:

1. Question: "how many cities sold more than 300 in the current slicer?"
2. VALUES of City gives Mumbai and Pune. Mumbai sales 700 in 2025 (500+200). Pune sales 100.
3. FILTER keeps Mumbai only. COUNTROWS out: 1.
4. Phone bill question: "how many months is the bill above 500?" FILTER keeps March 699. Number out: 1. Jan and Feb stay out.
5. Do not scan every sales slip with FILTER just to say City = Mumbai. The slicer, or CALCULATE ( [Total Sales], City = "Mumbai" ), already does that.

Click Pune. 2025 sales should fall to 100. Cities-above-300 should fall to blank or zero, because 100 is not above 300. If the card still says Mumbai, the formula replaced the slicer instead of working inside it.

What do I need before this guide?

• CALCULATE and filter context.
• FILTER inside CALCULATE.
• COUNTROWS.
• Course: FILTER.

Before and after (look at the tables first)

Scalar sibling, table sibling

Sales 2025 =
CALCULATE (
    [Total Sales],
    'Date'[Year] = 2025
)

That card is a number. The table form is:

Sales Rows 2025 =
CALCULATETABLE (
    Sales,
    'Date'[Year] = 2025
)

1. You can use CALCULATETABLE as a calculated table in the model when you truly need those rows stored.
2. Inside a measure, you usually consume it: COUNTROWS of it, or another function that wants a table.
3. A card cannot display a table. If the goal is “sales in 2025”, stay with CALCULATE.
4. Filter arguments behave like CALCULATE: they modify filter context. USERELATIONSHIP belongs in that family too, inside CALCULATE or CALCULATETABLE.

FILTER when the test is a measure

“How many cities are above 100000?” cannot be a simple column filter. The test is [Total Sales], which is a measure.

Cities Above =
VAR Bar = 100000
RETURN
    COUNTROWS (
        FILTER (
            VALUES ( Customer[City] ),
            [Total Sales] > Bar
        )
    )

Vertical reading:

1. VALUES builds the list of cities in the current context. Keep this list small.
2. FILTER keeps a city only when its sales measure beats the bar.
3. COUNTROWS turns the surviving table into a number the card can show.
4. Change the City slicer. The list of candidates changes, so the count should change.

The slow habit

Online Slow =
CALCULATE (
    [Total Sales],
    FILTER ( Sales, Sales[Channel] = "Online" )
)

1. This can be correct and still be a bad habit. It iterates fact rows.
2. The lighter shape is CALCULATE ( [Total Sales], Sales[Channel] = "Online" ).
3. Reach for FILTER when the condition needs a measure, or logic a boolean column filter cannot say.
4. If you also need to intersect with a slicer on that same column, that is KEEPFILTERS, not a reason to scan the table.

Ghabru naka — if the measure returns a table error on a card, you forgot to aggregate the table down to a scalar.

Mistakes and calm fixes