Ravindra BagaleCourses & study guides Track your progress

Chapter 46

46.1 Iterators, Tables in DAX and Virtual Tables

By Ravindra Bagale · Power BI deep series

Chala mitrano — sometimes SUM is enough; sometimes you must walk row by row with SUMX. Virtual tables are temporary — use them clearly.

Fictional sample data for learning. All company names and brands belong to their owners. We use the names only so students can understand. Numbers on this page are made up.
After this lesson you can
  • Choose SUM vs SUMX with a reason.
  • Build a filtered virtual table with FILTER.
  • Shape columns with ADDCOLUMNS / SELECTCOLUMNS.
  • Summarise with SUMMARIZE carefully.
  • Keep virtual tables readable and small.
  • Explain iterators in an interview.

Start with a problem (fictional). Carlos Mendes (Lisbon retail analytics, fictional) needed average discount per invoice, not discount of averages. SUM failed. SUMX over invoices fixed it. Then he overgrew a SUMMARIZE of every product daily and the report froze — Priya taught smaller virtual tables.

Sample layout — fictional data: SUM vs SUMX to virtual tables
Sample layout — fictional data (not a real Service screenshot)

1. SUM vs SUMX — aggregator vs iterator

Why: Average of ratios is not ratio of averages — iterators matter.

What: SUM adds a column. SUMX iterates a table, evaluates an expression per row, then sums.

How — pick the tool:

Vertical steps (one under another)

  1. Simple column add? SUM(Sales[Amount]).
  2. Expression per row (qty * price, conditional)? SUMX.
  3. Write the table you iterate (Sales, or a filtered subset).
  4. Test on 10 rows before the full model.
  5. Prefer a clean column in the model if used everywhere.

Tiny example: LineAmount = SUMX(Sales, Sales[Qty] * Sales[UnitPrice]) — fictional INR.

Ravindra Bagale's Tip

Friends, if the math needs per-row logic, SUMX (or a column) — not hope.

2. FILTER builds a virtual table

Why: You often need only rows that match a condition inside a measure.

What: FILTER returns a table of rows matching a condition — usually consumed by an iterator or CALCULATE.

How — filter then aggregate:

Vertical steps (one under another)

  1. Start from a base table (Sales).
  2. FILTER(Sales, Sales[Qty] > 0).
  3. Pass into SUMX or use as CALCULATE filter carefully.
  4. Keep predicates simple.
  5. Avoid FILTER on huge unneeded columns.

Tiny example: SUMX(FILTER(Sales, Sales[City]="Nashik"), Sales[Amount]) — teaching form; prefer model relationships when possible.

Ravindra Bagale's Tip

Friends, relationships + CALCULATE often beat hand-FILTER on fact tables — know both.

3. ADDCOLUMNS and SELECTCOLUMNS

Why: You need helper columns on a virtual table without permanently changing the model.

What: ADDCOLUMNS adds columns; SELECTCOLUMNS builds a shaped table of chosen columns.

How — shape lightly:

Vertical steps (one under another)

  1. ADDCOLUMNS(City, "Sales", [Sales]) for a small city list.
  2. SELECTCOLUMNS to return only keys you need.
  3. Do not materialise monster tables in measures.
  4. Name virtual columns clearly.
  5. Prefer calculated columns only when reused heavily.

Tiny example: Tiny city table with a Sales measure column for a top-N visual pattern (concept).

Ravindra Bagale's Tip

Friends, virtual columns are free only until cardinality explodes — watch row counts.

4. SUMMARIZE — group with care

Why: Grouped virtual tables power many patterns — and many slow reports.

What: SUMMARIZE groups by columns; newer patterns may use SUMMARIZECOLUMNS — learn both names.

How — group small:

Vertical steps (one under another)

  1. Summarise at City grain, not City x Product x Day unless needed.
  2. Add measures carefully after grouping.
  3. Test query cost mentally: how many groups?
  4. Push heavy grouping to the model/ETL when stable.
  5. Document grain in the measure name.

Tiny example: Summarise Sales by City for a small rank table — not every SKU (fictional).

Ravindra Bagale's Tip

Friends, if SUMMARIZE returns hundreds of thousands of groups, redesign.

5. Keep virtual tables readable

Why: Unreadable DAX is unmaintainable DAX.

What: Break complex table expressions into variables (VAR) with clear names.

How — use VAR:

Vertical steps (one under another)

  1. VAR Cities = VALUES('City'[CityKey]).
  2. VAR Ranked = ...
  3. RETURN SUMX(Ranked, ...)
  4. One idea per variable.
  5. Comment grain in one short line.

Tiny example: Kabir 40-line nested FILTER becomes 4 named VARs — Training can review.

Ravindra Bagale's Tip

Friends, VAR is for clarity first — optimise after it is correct.

6. Interview: iterators in 45 seconds

Why: They ask SUM vs SUMX and what a virtual table is.

What: SUM adds a column; SUMX evaluates per row; virtual tables are temporary results inside DAX.

How — deliver cleanly:

Vertical steps (one under another)

  1. Give qty x price SUMX example.
  2. Say FILTER returns a table.
  3. Mention VAR for readability.
  4. Warn about large SUMMARIZE grains.
  5. Prefer model relationships when they replace filters.

Tiny example: Close: I iterate only when the expression needs row logic; otherwise SUM.

Ravindra Bagale's Tip

Friends, say grain out loud — City vs City x Day — interviewers relax.

Quiz (5 questions)

1. Use SUMX when:

2. FILTER returns:

3. ADDCOLUMNS is for:

4. Danger of huge SUMMARIZE grains:

5. VAR in DAX mainly helps you:

Practice tasks (12) — hints only, no full answers

  1. Task. Write SUM vs SUMX with qty x price example.
    Hint: section 1.
  2. Task. When is a model column better than SUMX everywhere?
    Hint: reuse.
  3. Task. FILTER Sales to Qty > 0 — sketch measure.
    Hint: section 2.
  4. Task. Why prefer relationships over fact FILTER when possible?
    Hint: section 2 tip.
  5. Task. ADDCOLUMNS on City with [Sales] — what grain?
    Hint: city.
  6. Task. Name a bad SUMMARIZE grain for a morning card.
    Hint: section 4.
  7. Task. Refactor a nested expression into 3 VARs (names only).
    Hint: section 5.
  8. Task. Interview 45-second SUM vs SUMX.
    Hint: section 6.
  9. Task. Carlos average discount — why SUM failed.
    Hint: story.
  10. Task. SELECTCOLUMNS use-case in one line.
    Hint: section 3.
  11. Task. How do you estimate group count before SUMMARIZE?
    Hint: cardinality.
  12. Task. List two signs a virtual table is too big.
    Hint: slow / memory.

Ravi Sir's cards (Excel card style)

Standalone cards — no tip or photo sections. QR points to this lesson. Download PNG to revise offline.

Interview Q&A: When do you use SUMX instead of SUM?
Download interview-card PNG
Comparison Card: SUM vs SUMX
Download comparison-card PNG
Revision Card: Virtual tables checklist
Download revision-card PNG

Think like a professional

In a real tenant, rights and sharing are controlled by Fabric / Power BI admins. Always follow your company policy. This lesson uses fictional people and numbers so you can practise the ideas safely.