Table functions and virtual tables

01The problem

The commercial director asks three questions for the quarterly review:

1. How many customers spent more than ₦10m in 2025?
2. How many new customers did we win in 2026?
3. How many customers buy from all four of our categories?

None of them is a sum or an average of a column. Each one is "count the customers that meet a condition", and the condition is itself a calculation. To answer them, a measure has to build a table of customers in memory, test each one and count the survivors. That's what table functions are for.

02The concept

Some DAX functions return a table rather than a value. You can't show a table in a card, but you can count it, iterate it or use it as a filter.

The pattern behind all three questions:

COUNTROWS ( FILTER ( VALUES ( orders[customer_id] ), <condition per customer> ) )

FILTER iterates the customers, so the condition runs in a row context. Use a measure in the condition and context transition calculates it for each customer.

Filter direction matters in table logic too

You might try to count each customer's categories with CALCULATE ( DISTINCTCOUNT ( products[category] ) ). It returns 4 for everyone: filters flow from products to orders, never from orders back to products. Count the categories through the fact table instead:

COUNTROWS ( SUMMARIZE ( orders, products[category] ) )

SUMMARIZE lists the categories that actually appear in the customer's order lines.

DAX query view

Power BI Desktop's DAX query view (the fourth icon on the left) runs a query and shows the resulting table, so you can see a virtual table before you count it:

EVALUATE
ADDCOLUMNS (
    VALUES ( customers[customer_name] ),
    "Revenue 2025", CALCULATE ( [Revenue], 'Date'[Year] = 2025 )
)
ORDER BY [Revenue 2025] DESC

03Example

Big Customers =
COUNTROWS (
    FILTER ( VALUES ( orders[customer_id] ), [Revenue] > 10000000 )
)

New Customers =
VAR PeriodStart = MIN ( 'Date'[Date] )
RETURN
    COUNTROWS (
        FILTER (
            VALUES ( orders[customer_id] ),
            CALCULATE ( MIN ( orders[order_date] ), REMOVEFILTERS ( 'Date' ) ) >= PeriodStart
        )
    )

All-Category Customers =
COUNTROWS (
    FILTER (
        VALUES ( orders[customer_id] ),
        CALCULATE ( COUNTROWS ( SUMMARIZE ( orders, products[category] ) ) ) = 4
    )
)

New Customers takes the customers who bought in the period, works out each one's first ever order date (removing the date filter), and keeps those whose first order falls inside the period. PeriodStart is a variable because it must be the start of the visual's period, captured before FILTER starts iterating.

04Walkthrough

1. Open DAX query view and run the EVALUATE query from the concept section. You'll see 90 rows: the virtual table Big Customers filters.
2. Add the three measures, and put them in a table by Date[Year].
3. Check Big Customers for 2025 in DAX query view: add FILTER ( …, [Revenue 2025] > 10000000 ) around the ADDCOLUMNS and count the rows.
4. Try the wrong version of the category count, CALCULATE ( DISTINCTCOUNT ( products[category] ) ) = 4. Every buying customer passes. Then switch back.
5. Add customers[channel] to the table. Which channel do the new customers of 2026 come from?

05Practice

06More practice

07Check your understanding