Table manipulation functions

1. Table manipulation functions

Hi again! Are you starting to feel like a DAX ninja yet? I'm Maarten, and I'll be the second instructor for this course. In this lesson, we'll talk about table manipulation functions.

2. Table manipulation functions overview

You've already seen table manipulation functions, such as DISTINCT(), which removes duplicates, and SELECTCOLUMNS(), which returns a table with one or more selected columns. Now, two more table manipulation functions will be added to your toolbox. The first one is ADDCOLUMNS(), which takes the input table and adds one or more selected columns to it. The second one is SUMMARIZE(), which returns a summary table for the requested totals over a set of groups. Let's cover them one by one.

3. ADDCOLUMNS()

ADDCOLUMNS() returns a table that contains the original input table extended with one or more new columns. For example, you can calculate and add a Profit column directly to a table containing Revenue and Costs.

4. ADDCOLUMNS()

The output will contain the original table, appended with the newly calculated column.

5. ADDCOLUMNS()

This is the main difference with SELECTCOLUMNS(), which only returns the selected column(s). Notice that both functions require the same arguments.

6. SUMMARIZE()

The second new table manipulation function is SUMMARIZE(). By definition, it returns a summary table for the requested totals over a set of groups. Let's break that down to fully understand this.

7. SUMMARIZE()

First, the function takes an input table, in this example the Amounts table, displayed on the right. Then, that table is grouped by one or more columns, year and category in this case. Finally, you can create one or more new columns, by specifying a name and a corresponding expression. Here, the Total Amounts is calculated by summing the amounts in each group.

8. SUMMARIZE()

The result is a summary table, with the Total Amount for each combination of Year and Category. Note that Total Amount is different for each row, because it is evaluated in the filter context of Year and Category.

9. SUMMARIZE() best practices

Since SUMMARIZE() is a complex function for querying your data with many options for arguments, it has to be used with caution. Although the function can create new columns, it might result in unexpected results based on the context. Therefore, it is recommended to create new columns with an ADDCOLUMNS() function wrapped around SUMMARIZE(). ADDCOLUMNS() will always use the row context, and will produce the same, but consistent results compared to the SUMMARIZE() call on the left.

10. Let's practice!

Let's summarize this video with an exercise.

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.