Skip to content
Tutorials Center
My learning

Topics

Lesson 34 of 56 ยท DAX

DAX Table Functions: SUMMARIZE, GROUPBY, CROSSJOIN, UNION and LOOKUPVALUE

Learn the DAX table functions: ADDCOLUMNS, SUMMARIZE, GROUPBY, CROSSJOIN, UNION, EXCEPT, TREATAS, LOOKUPVALUE and TOPN, with pandas stand-ins.

Many DAX functions do not return a number. They return a table: a filtered set of rows, a grouped summary, a combination of two lists. Table functions are the plumbing of advanced DAX. You wrap them in an iterator such as SUMX or COUNTROWS to get a value, you pass them to CALCULATE as filters, or you use them to build calculated tables. Knowing them turns "I cannot do that in DAX" into "I need the right table first".

This lesson covers the functions that build, combine and look up tables. The DAX is read-only. The runnable parts are pandas stand-ins that return the same rows, so you can see what each function produces. Pandas also makes the main difference visible: DAX tables are virtual, evaluated on the fly in the current filter context, and are not stored unless you assign them to a calculated table.

Table expressions and virtual tables

DAX
-- as a calculated table
Top Products = TOPN ( 5, Product, [Total Sales], DESC )

-- as an argument of an aggregation
Customers With Sales = COUNTROWS ( FILTER ( Customer, [Total Sales] > 0 ) )

-- as a filter in CALCULATE
Sales Of Top 5 = CALCULATE ( [Total Sales], TOPN ( 5, ALL ( Product[Name] ), [Total Sales] ) )

You already know FILTER, VALUES, ALL and DISTINCT from CALCULATE and Filter Context and Ranking, Top N and Percent of Total. The functions below add shaping and combining.

Adding and picking columns: ADDCOLUMNS, SELECTCOLUMNS

DAX
Customer Sales =
ADDCOLUMNS (
    VALUES ( Customer[Name] ),
    "Sales", [Total Sales],
    "Orders", [Order Count]
)

Slim =
SELECTCOLUMNS (
    Product,
    "Code", Product[SKU],
    "Name", Product[Name]
)

ADDCOLUMNS(table, name, expression, ...) keeps all columns of the source table and adds calculated ones. The expression is evaluated for each row with context transition (a measure there is evaluated for that row's customer, see CALCULATE and Filter Context). SELECTCOLUMNS returns only the columns you name, which can be renamed or calculated.

Grouping: SUMMARIZE, SUMMARIZECOLUMNS, GROUPBY

DAX
By Region And Year =
SUMMARIZE ( Sales, Customer[Region], 'Date'[Year] )

With Totals =
ADDCOLUMNS (
    SUMMARIZE ( Sales, Customer[Region], 'Date'[Year] ),
    "Sales", [Total Sales]
)

Query Style =
SUMMARIZECOLUMNS ( Customer[Region], 'Date'[Year], "Sales", [Total Sales] )

Group Tables =
GROUPBY ( Sales, Sales[Region], "Total", SUMX ( CURRENTGROUP ( ), Sales[Amount] ) )

Three functions, three roles:

Function Use it for Notes
SUMMARIZE(table, groupCols...) the distinct combinations of group columns that exist in the table Group columns can come from related tables. Wrap in ADDCOLUMNS to add measures; adding extension columns inside SUMMARIZE is legacy style and can give wrong answers.
SUMMARIZECOLUMNS(groupCols, [filters], "name", expr...) what visuals use internally; best in queries and calculated tables Drops groups with no values in the expressions; has limits inside measures that run in a row context.
GROUPBY(table, groupCols, "name", expr) grouping a virtual table, using CURRENTGROUP() and an X iterator Cannot use a plain measure as the aggregate; the aggregate must be iterator-based.

A good rule: use SUMMARIZE for the grouping and ADDCOLUMNS for the numbers.

Try it: grouping in pandas

Python Stand-in: SUMMARIZE, ADDCOLUMNS and GROUPBY

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Combining tables: CROSSJOIN, UNION, INTERSECT, EXCEPT

DAX
All Combinations = CROSSJOIN ( Region[Region], Product[Category] )

Both Lists = UNION ( Online[CustomerID], Store[CustomerID] )

In Both = INTERSECT ( Online[CustomerID], Store[CustomerID] )

Online Only = EXCEPT ( Online[CustomerID], Store[CustomerID] )
  • CROSSJOIN pairs every row of one table with every row of the other (the Cartesian product). Use it to build a grid of all region and category combinations, so that combinations without sales still appear. Two tables of 1,000 rows give 1,000,000 rows, so watch the size.
  • UNION(t1, t2, ...) stacks tables by column position, not by name, and keeps duplicates. Wrap it in DISTINCT to remove them. The tables must have the same number of columns. It is the DAX form of the append you know from Merging and Appending Queries.
  • INTERSECT returns rows found in both; EXCEPT returns rows of the first not found in the second. The "online only" customers is a typical EXCEPT.
Python Stand-in: CROSSJOIN, UNION by position, INTERSECT, EXCEPT

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The last table shows the common trap: Eli's name lands in the City column, because DAX takes the second column of the second table to be the "City" of the first. Check that your tables list their columns in the same order, or use SELECTCOLUMNS to put them in one order first.

Looking things up: LOOKUPVALUE, TREATAS, NATURALINNERJOIN

DAX
Region Of Customer =
LOOKUPVALUE ( Region[Name], Region[Code], Sales[RegionCode], "Unknown" )

Sales For List =
CALCULATE ( [Total Sales], TREATAS ( VALUES ( Budget[Product] ), Product[Name] ) )

LOOKUPVALUE(resultColumn, searchColumn, value, ..., [alternative]) finds the row where the search columns match the values and returns one column. It works without a relationship, which makes it useful in calculated columns on tables that cannot be related. It fails with an error if more than one distinct result matches, unless you provide the alternative value. If a relationship exists, RELATED is faster and clearer; see Relationships, Cardinality and Filter Direction.

TREATAS(table, col1, col2, ...) takes the values of a table expression and applies them as a filter on other columns, as if there were a relationship. It is the standard way to create a virtual relationship: filter Sales by the products listed in a budget table that has no relationship to the Product dimension.

NATURALINNERJOIN and NATURALLEFTOUTERJOIN join two tables on columns that have the same name and come from the same source column (matching lineage). They are rare in practice because star-schema relationships already do this.

Try it: lookups with and without a relationship

Python Stand-in: LOOKUPVALUE and TREATAS

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Ranking and sampling: TOPN, DISTINCT, GENERATESERIES, ROW, DATATABLE

DAX
Top 3 Customers = TOPN ( 3, VALUES ( Customer[Name] ), [Total Sales], DESC )

Parameter Values = GENERATESERIES ( 0, 100, 10 )

One Row = ROW ( "Total", [Total Sales], "Orders", [Order Count] )

Mapping =
DATATABLE (
    "Code", STRING, "Label", STRING,
    { { "N", "North" }, { "S", "South" } }
)

TOPN returns the top rows by an expression (and may return more than N rows when there are ties). GENERATESERIES(start, end, step) builds a numeric (or date) series, which is the basis of what-if parameters and slider tables. ROW creates a one-row table, and DATATABLE types a small table of constants straight into a calculated table. DISTINCT and VALUES give unique values of a column; VALUES can also include a blank row that appears because of an unmatched relationship.

Seeing a table: the DAX query view

You cannot "see" a table expression in a measure, but you can in DAX query view (the fourth icon in Desktop's left bar), where EVALUATE prints any table expression, as in Debugging and Optimizing DAX:

DAX
EVALUATE
TOPN ( 3, SUMMARIZECOLUMNS ( Product[Name], "Sales", [Total Sales] ), [Sales], DESC )

Use this to test each function above before building it into a measure. Checking the intermediate table is the fastest way to debug a measure that returns a wrong number.

Practical advice

  • Build the grouping with SUMMARIZE or VALUES, then attach numbers with ADDCOLUMNS.
  • Use TREATAS rather than a bi-directional or many-to-many relationship for one-off links, such as comparing actuals with a budget.
  • Check the column order before a UNION.
  • Prefer RELATED or a real relationship to LOOKUPVALUE when one exists.
  • Large CROSSJOIN results are expensive. Filter first.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. How does UNION match the columns of its input tables?

    UNION stacks the tables by position, so two tables with the same columns in a different order mix up their data.
  2. 2. Which function applies the values of a table as a filter on a column of another table without a relationship?

    TREATAS maps the columns of a table expression to other columns and filters them, which creates a virtual relationship.
  3. 3. What is the recommended way to add a measure to a grouped table?

    SUMMARIZE does the grouping, and ADDCOLUMNS adds the calculated values, which avoids the surprises of extension columns in SUMMARIZE.
  4. 4. LOOKUPVALUE finds two different results for one search value. What happens?

    The search must identify one distinct value. If it does not, you get an error, or the alternative value if you provided one.
  5. 5. How many rows does CROSSJOIN of a 4-row table and a 5-row table return?

    CROSSJOIN returns every pairing, so the number of rows is 4 times 5.

Summary

  • Table functions return virtual tables that you wrap in iterators, pass to CALCULATE or store as calculated tables.
  • ADDCOLUMNS adds columns, SELECTCOLUMNS picks them, SUMMARIZE groups, SUMMARIZECOLUMNS suits queries and GROUPBY groups virtual tables with CURRENTGROUP().
  • CROSSJOIN, UNION (by position), INTERSECT and EXCEPT combine tables.
  • LOOKUPVALUE looks up one value without a relationship, and TREATAS creates a virtual relationship.
  • TOPN, GENERATESERIES, ROW and DATATABLE create and rank small tables; test them with EVALUATE in DAX query view.

Next, move from numbers to pictures in Choosing the Right Visual.