How to Filter Summarize Table in Power BI

Creating summary tables with Power BI's SUMMARIZE function is a game-changer for data analysis, but it's only half the battle. To get to the real insights, you often need to filter those summarized results to focus on what matters most. This tutorial will walk you through exactly how to filter tables created with the SUMMARIZE function using two powerful DAX methods: FILTER and CALCULATETABLE.

What is the SUMMARIZE Function in Power BI?

Before diving into filtering, let's quickly review the SUMMARIZE function itself. At its core, SUMMARIZE is a DAX function that acts like a powerful "Group By" operation. Usually, you feed it a table, specify columns to group by, and define calculations (or aggregations) for each group. The result is a new summary table containing only those grouped columns and your calculated results.

The basic syntax looks like this:

SUMMARIZE(
    <table>, 
    <groupBy_columnName1>, 
    [<groupBy_columnName2>], 
    …, 
    "<NewColumnName>", 
    <expression>
)

Let's break that down:

• <table>: The source table you want to summarize, for example, your 'Sales' table.
• <groupBy_columnName>: The column(s) you want to group your data by, such as 'Product Category' or 'Region'.
• "<NewColumnName>": The name for your new calculated column, enclosed in quotes (e.g., "Total Revenue").
• <expression>: The DAX calculation for each group, such as SUM(Sales[SaleAmount]).

For example, if you have a Sales table and want to see total sales per product category:

SalesByCategory = 
SUMMARIZE(
    Sales,
    Sales[Product Category],
    "Total Sales", SUM(Sales[SaleAmount])
)

This creates a simple table listing each product category and its total sales. But what if you only want categories with over $100,000 in sales? Or restrict to a specific sales region? That's where filtering becomes essential.

Why Filter a Summarized Table?

You can't always work with the entire summarized dataset. Filtering allows transforming broad overviews into focused, actionable insights. Common scenarios include:

• Focusing on Top Performers: Showing only products, regions, or salespeople exceeding a performance threshold (e.g., sales > $50,000).
• Analyzing Specific Segments: For example, sales data only for the "East" region.
• Cleaning Data: Excluding categories or groups, such as internal or test sales.
• Improving Performance: Filtering before summarization reduces dataset size and accelerates report performance.

How to Filter a Summarized Table: Two Key Methods

DAX provides two main functions for filtering table expressions like SUMMARIZE: the FILTER function and the CALCULATETABLE function. They are both powerful but work differently.

Method 1: Using the FILTER Function

The FILTER function is an iterator—it steps through a table row by row, evaluating a condition, and returns a new table containing only the rows that meet the condition.

Syntax:

FILTER(<table>, <filter_expression>)

Use FILTER when filtering the result of SUMMARIZE. This is especially useful when filter conditions depend on the aggregated columns created within SUMMARIZE.

Example: Filtering Based on an Aggregated Value

Suppose you want to see only product categories with more than $100,000 in sales. Since "Total Sales" is a new column created after SUMMARIZE, you can filter the summarized table like this:

TopPerformingCategories = 
FILTER(
    SUMMARIZE(
        Sales,
        Sales[Product Category],
        "Total Sales", SUM(Sales[SaleAmount])
    ),
    [Total Sales] > 100000
)

This works as follows:

1. SUMMARIZE creates a virtual table with 'Product Category' and 'Total Sales'.
2. FILTER iterates over each row.
3. Checks if [Total Sales] > 100000.
4. Returns only the rows meeting this condition.

Note: We refer to [Total Sales] directly inside FILTER without specifying the table name because it’s a column in the current row context.

Method 2: Using the CALCULATETABLE Function

CALCULATETABLE modifies the filter context before evaluating the table expression. Think of it as pre-filtering the original data, then performing SUMMARIZE on that filtered dataset.

Syntax:

CALCULATETABLE(<table_expression>, <filter1>, <filter2>, …)

Use CALCULATETABLE when you want to filter original data columns prior to grouping and summarization.

Example: Summarizing Data for a Specific Region

Suppose you want total sales per month only for the 'West' region:

WestRegion_MonthlySales =
CALCULATETABLE(
    SUMMARIZE(
        Sales,
        Sales[Month],
        "Total Sales", SUM(Sales[SaleAmount])
    ),
    Sales[Region] = "West"
)

Process:

1. CALCULATETABLE applies the filter Sales[Region] = "West" to the Sales data.
2. SUMMARIZE then groups only the filtered data by month, calculating total sales.
3. The result is a table with the monthly sales figures for the West region.

This method is often more efficient on large datasets because it filters data before aggregation.

FILTER vs. CALCULATETABLE: Which to Choose?

Answering this depends on your goal:

• Use CALCULATETABLE if:
• Use FILTER if:

Advanced Tips and Common Pitfalls

Tip 1: Combining Both Methods

You can nest these functions for complex filtering. For example, to find the top product categories (sales > $25,000) within the North region:

North_TopCategories_Combined = 
FILTER(
    CALCULATETABLE(
        SUMMARIZE(
            Sales, 
            Sales[Product Category],
            "Total Sales", SUM(Sales[SaleAmount])
        ),
        Sales[Region] = "North"
    ),
    [Total Sales] > 25000
)

This filters the dataset to North region first, then filters the summarized result for high-sales categories.

Tip 2: Use Variables (VAR) for Readability

On complex formulas, variables improve clarity:

North_TopCategories_Variables = 
VAR NorthRegionSummary = 
    CALCULATETABLE(
        SUMMARIZE(
            Sales, 
            Sales[Product Category],
            "Total Sales", SUM(Sales[SaleAmount])
        ), 
        Sales[Region] = "North"
    )
RETURN
    FILTER(
        NorthRegionSummary, 
        [Total Sales] > 25000
    )

This highlights each step, making the logic clearer.

Pitfall: Using CALCULATETABLE with an Aggregated Column

Trying to filter on an aggregated column created inside SUMMARIZE using CALCULATETABLE's filter argument will not work:

// This will cause an error
CALCULATETABLE(
    SUMMARIZE(...),
    [Total Sales] > 100000
)

Because [Total Sales] doesn't exist in the original Sales table. It’s only created after SUMMARIZE. Use FILTER for such conditions.

Final Thoughts

Mastering how to filter summarized tables in Power BI is essential for creating focused, insightful reports. Understanding when to use FILTER (after summarization) versus CALCULATETABLE (before summarization) will greatly enhance your DAX skills. Remember:

• Use CALCULATETABLE to filter before grouping.
• Use FILTER to filter after grouping, on the results of SUMMARIZE.

With these techniques, you can handle almost any grouping and filtering scenario in Power BI.

While DAX provides deep power, tools like Graphed can help, enabling you to generate meaningful insights through natural language queries, easing the complexity of data analysis. Just ask in plain English, and let the tool do the heavy lifting for you.