Fix Blanks in Power BI SUMMARIZECOLUMNS with SELECTEDVALUE

A user in a Power BI community recently asked: “I’m trying to create a virtual table inside a measure using SUMMARIZECOLUMNS. I want to filter this table based on a slicer using SELECTEDVALUE, but the measure keeps returning blank or ignoring my selection. Why does SELECTEDVALUE work in a Card visual but fail inside my DAX table variable?”

I have encountered this specific behavior many times. The issue stems from the fact that SUMMARIZECOLUMNS is a highly optimized query function that does not support context transition and often struggles to “see” the filter context provided by measures when nested inside other functions.

The Quick Fix: The “Variable First” Pattern

The most reliable way to fix this is to capture your slicer value in a Variable (VAR) before you call the SUMMARIZECOLUMNS function. By evaluating the selection outside the virtual table constructor, you pass a static value into the engine, bypassing the context transition failure.

Example — verify in your environment (Tested on Power BI Desktop February 2024 version):

Total Sales for Category =
VAR SelectedCat = SELECTEDVALUE('Product'[Category]) -- Capture value FIRST
VAR VirtualTable =
    SUMMARIZECOLUMNS(
        'Sales'[CustomerID],
        KEEPFILTERS('Product'[Category] = SelectedCat), -- Use the variable here
        "SalesAmt", SUM('Sales'[Amount])
    )
RETURN
SUMX(VirtualTable, [SalesAmt])

---

Step-by-Step Breakdown

When you use SUMMARIZECOLUMNS inside a measure, it operates differently than it does in a calculated table. It does not automatically inherit the filter context of the visual it is placed in unless you explicitly define the filters.

Component	Purpose	Why it matters
VAR SelectedCat	Evaluation	Evaluates SELECTEDVALUE in the original filter context of the report.
SUMMARIZECOLUMNS	Table Constructor	Generates a grouped virtual table based on specified columns.
KEEPFILTERS	Filter Preservation	Ensures the filter on ‘Category’ respects other existing report filters.
SUMX	Aggregation	Iterates over the virtual table to produce a final scalar value for the measure.

Detailed Implementation Steps:

1. Define the Variable: Always start your measure by declaring a variable for your slicer value. This “locks in” the user’s choice from the UI.
2. Construct the Virtual Table: Use SUMMARIZECOLUMNS to group your data. If you need to filter this table by your variable, do not put the variable directly in a FILTER function inside SUMMARIZECOLUMNS. Instead, use it in the filter arguments of the function itself.
3. Use KEEPFILTERS: If your visual already has filters (like a date range or region), wrapping your variable filter in KEEPFILTERS prevents SUMMARIZECOLUMNS from overwriting those other active filters.
4. Aggregate the Result: Since SUMMARIZECOLUMNS returns a table, you must use an iterator function like SUMX, COUNTX, or AVERAGEX to turn that table back into a single number for your visual.

---

Common Traps and Considerations

1. Why not use SUMMARIZE?

You might be tempted to use SUMMARIZE instead of SUMMARIZECOLUMNS. While SUMMARIZE does support context transition, it is generally slower and can produce unexpected results when performing grouped calculations. The industry standard is to use SUMMARIZE only for grouping columns and ADDCOLUMNS for the actual math.

2. The “Filter Context” Ghost

SUMMARIZECOLUMNS is notoriously “blind” to the filter context if it is called inside a measure that is already being iterated (like inside a SELECTCOLUMNS or another SUMX). If your quick fix still returns blanks, wrap your SUMMARIZECOLUMNS in a CALCULATETABLE function to force the engine to respect the surrounding filters.

3. Handling Multiple Selections

If your slicer allows multiple selections, SELECTEDVALUE will return blank. In this case, you should switch your logic to use VALUES:

-- Tested on Power BI Desktop (2024)
VAR SelectedCategories = VALUES('Product'[Category])
VAR VirtualTable =
    CALCULATETABLE(
        SUMMARIZECOLUMNS(
            'Sales'[CustomerID],
            "SalesAmt", SUM('Sales'[Amount])
        ),
        TREATAS(SelectedCategories, 'Product'[Category])
    )

---

Related Questions

What if I need to apply this to filtered rows in a specific visual? If you want the virtual table to respect the specific rows of a Table or Matrix visual, SUMMARIZECOLUMNS is often the wrong choice inside a measure. Instead, use the ADDCOLUMNS(SUMMARIZE(...), ...) pattern, which was designed specifically to work within the existing filter context of a visual.

Can I use this logic in a Calculated Table? Calculated tables only refresh on data load, not when a user clicks a slicer. If you use SELECTEDVALUE in a Calculated Table (not a measure), it will almost always return blank or the default value because no user interaction is happening at the time of the table’s creation.

Does using variables improve performance? Yes. By using a variable, you ensure that SELECTEDVALUE is calculated exactly once. If you were to place the function directly inside the table constructor or an iterator, the engine might attempt to re-evaluate that selection for every row of the virtual table, leading to significant “DAX noise” and slower report rendering.